1. cast()的用法 MySQL中大多數類型轉化可以使用cast(值 as 目標類型)的語法來完成。 cast後面必須緊接著左括弧： mysql> select cast(123 as char); + + | cast(123 as char) | + + | 123 | + + 1 row ...
1. cast()的用法
MySQL中大多數類型轉化可以使用cast(值 as 目標類型)的語法來完成。
cast後面必須緊接著左括弧：
mysql> select cast(123 as char);
+-------------------+
| cast(123 as char) |
+-------------------+
| 123 |
+-------------------+
1 row in set (0.00 sec)
cast和左括弧之間有一個空格時報錯：
mysql> select cast (123 as char);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'char)' at line 1
mysql>
2. 數值類型和字元串類型的轉換
使用cast()將數值轉換為字元串類型：
mysql> select cast(123.456 as char);
+-----------------------+
| cast(123.456 as char) |
+-----------------------+
| 123.456 |
+-----------------------+
1 row in set (0.00 sec)
轉化為字元串時，參數只能使用char，而不能使用varchar。使用varchar則報錯：
mysql> select cast(123 as varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10))' at line 1
mysql> select cast(123 as char(10));
+-----------------------+
| cast(123 as char(10)) |
+-----------------------+
| 123 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select cast(123 as char(2));
+----------------------+
| cast(123 as char(2)) |
+----------------------+
| 12 |
+----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect CHAR(2) value: '123' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
可以使用char(N)指定寬度，超過寬度的字元自動截斷。
使用cast()將字元串轉換為數值類型。
mysql> select cast('123' as decimal(10,0)) + cast('456' as decimal(10,0));
+-------------------------------------------------------------+
| cast('123' as decimal(10,0)) + cast('456' as decimal(10,0)) |
+-------------------------------------------------------------+
| 579 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)
轉化為數值時，參數只能是decimal,而不能是int等。
mysql> select cast('123' as decimal);
+-------------------------+
| cast('123' as decimal) |
+-------------------------+
| 123 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select cast('123' as decimal(3,0));
+-----------------------------+
| cast('123' as decimal(3,0)) |
+-----------------------------+
| 123 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select cast('123' as decimal(2,0));
+-----------------------------+
| cast('123' as decimal(2,0)) |
+-----------------------------+
| 99 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast('123' as decimal(2,0))' at row 1 |
+---------+------+----------------------------------------------------------------------+
1 row in set (0.00 sec)
當超出表示範圍時，自動轉化為符合範圍要求的最接近的數值。
當使用decimal之外的類型作為目標類型時，則報錯：
mysql> select cast('123' as float(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float(10))' at line 1
mysql> select cast('123' as int);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int)' at line 1
mysql> select cast('123' as numeric(10,0));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'numeric(10,0))' at line 1
mysql>
3. 日期類型和其它類型之間的轉換
日期相關類型也可以使用cast()與字元串類型進行轉換。
mysql> select cast('2017-09-01' as date);
+----------------------------+
| cast('2017-09-01' as date) |
+----------------------------+
| 2017-09-01 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select cast('2017-09-01' as datetime);
+--------------------------------+
| cast('2017-09-01' as datetime) |
+--------------------------------+
| 2017-09-01 00:00:00 |
+--------------------------------+
1 row in set (0.01 sec)
mysql> select cast(now() as char);
+---------------------+
| cast(now() as char) |
+---------------------+
| 2017-09-01 17:38:24 |
+---------------------+
1 row in set (0.00 sec)
日期類型也可以通過date_format()函數取得格式化後的字元串。
mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2017-09-01 17:41:22 |
+----------------------------------------+
1 row in set (0.00 sec)
日期類型在一些函數中，可以隱式的轉換為字元串類型。
mysql> select concat(now(),'abc');
+------------------------+
| concat(now(),'abc') |
+------------------------+
| 2017-09-01 17:38:45abc |
+------------------------+
1 row in set (0.00 sec)
字元串類型在一些日期相關函數中也可以隱式的轉換為日期類型。
mysql> select year('2017-09-01');
+--------------------+
| year('2017-09-01') |
+--------------------+
| 2017 |
+--------------------+
1 row in set (0.00 sec)
4. JSON類型與其它類型的轉換
使用CAST進行類型轉換，如果結果是JSON類型，可使用JSON_TYPE()查看具體類型。
mysql> select json_type(cast('1' as json));
+------------------------------+
| json_type(cast('1' as json)) |
+------------------------------+
| INTEGER |
+------------------------------+
1 row in set (0.00 sec)
mysql> select json_type(cast("1" as json));
+------------------------------+
| json_type(cast("1" as json)) |
+------------------------------+
| INTEGER |
+------------------------------+
1 row in set (0.00 sec)
mysql> select json_type(cast("abc" as json));
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.
mysql> select json_type(cast('"abc"' as json));
+----------------------------------+
| json_type(cast('"abc"' as json)) |
+----------------------------------+
| STRING |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select json_type(cast('true' as json));
+---------------------------------+
| json_type(cast('true' as json)) |
+---------------------------------+
| BOOLEAN |
+---------------------------------+
1 row in set (0.00 sec)
mysql> select json_type(cast(true as json));
+-------------------------------+
| json_type(cast(true as json)) |
+-------------------------------+
| BOOLEAN |
+-------------------------------+
1 row in set (0.00 sec)
mysql> select json_type(cast('{"a":"123","b":"345"}' as json));
+--------------------------------------------------+
| json_type(cast('{"a":"123","b":"345"}' as json)) |
+--------------------------------------------------+
| OBJECT |
+--------------------------------------------------+
1 row in set (0.00 sec)
如果僅僅為了獲取JSON的具體類型，也可以不使用cast轉換，直接使用單引號括起來就可以。
mysql> select json_type('true');
+-------------------+
| json_type('true') |
+-------------------+
| BOOLEAN |
+-------------------+
1 row in set (0.00 sec)
mysql> select json_type('{"a":"123","b":"345"}');
+------------------------------------+
| json_type('{"a":"123","b":"345"}') |
+------------------------------------+
| OBJECT |
+------------------------------------+
1 row in set (0.00 sec)
但是，如果不使用單引號括起來，則報錯：
mysql> select json_type(true);
ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_type; a JSON
string or JSON type is required.
