MariaDB – 資料型別轉換函數

測試環境為 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)