设为首页收藏本站

IT技术擎 - 最棒的IT web技术交流社区

 找回密码
 注册为IT技术擎人

QQ登录

只需一步,快速开始

搜索
热搜: php h5 jquery
查看: 32|回复: 0

[mysql] MySQL基础知识06数据类型(六)类型转换

[复制链接]

1万

主题

1万

帖子

5万

积分

版主

Rank: 7Rank: 7Rank: 7

积分
52861
发表于 2018-5-13 12:15:10 | 显示全部楼层 |阅读模式

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.



上一篇:LNMP环境下安装freeradius+radius manager3.9
下一篇:jQuery 事件函数传参异常identifier starts immediately after numeric literal
该用户未在地球留下任何的痕迹

本版积分规则

QQ|小黑屋|帮助|IT技术擎 ( 沪ICP备15054863号  

GMT+8, 2018-8-22 15:47

Powered by Discuz! X3.2 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表