MySQL基础知识06数据类型(六)类型转换
2018-06-18 00:15:31来源：未知 阅读 ()
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.
标签：
版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有
- MySQL replace函数怎么替换字符串语句 2020-03-09
- PHP访问MySQL查询超时怎么办 2020-03-09
- mysql登录时闪退 2020-02-27
- MySQL出现1067错误号 2020-02-27
- mysql7.x如何单独安装mysql 2020-02-27
IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash
