MariaDB – 資料型別轉換函數

269 total views , 1 views today

測試環境為 CentOS 8 x86_64 虛擬機.

• CAST
  資料型別作轉換.

  CAST(expr AS type)

  MariaDB [(none)]> SELECT CAST(1.09 AS SIGNED); +----------------------+ | CAST(1.09 AS SIGNED) | +----------------------+ |                    1 | +----------------------+ 1 row in set (0.00 sec)

  可轉換型別如下
  BINARY , CHAR , DATE , DATETIME , DECIMAL[(M[,D])] , DOUBLE , FLOAT — From MariaDB 10.4.5 , INTEGER , SIGNED [INTEGER] , TIME , UNSIGNED [INTEGER]

• CONVERT
  資料型別作轉換.其中 CONVERT(expr, type) 與 CAST(expr AS type) 使用上是一樣的.

  CONVERT(expr,type), CONVERT(expr USING transcoding_name)

  MariaDB [(none)]> SELECT CONVERT(1.09,SIGNED); +----------------------+ | CONVERT(1.09,SIGNED) | +----------------------+ |                    1 | +----------------------+ 1 row in set (0.00 sec)

  CONVERT 除了可以轉換型別,還可以做字符集編碼轉換,當在做資料比對 (LIKE) 時編碼不同時是無法比對的,如下的錯誤訊息.

  ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'

  這時候需透過 CONVERT(expr USING transcoding_name) 做不同的字符集轉換.

  MariaDB [(none)]> SELECT CONVERT('abc' USING utf8); +---------------------------+ | CONVERT('abc' USING utf8) | +---------------------------+ | abc                       | +---------------------------+ 1 row in set (0.00 sec)