--字符开头,直接是0
xxxx.test> select cast('a1' as unsigned int) astest ;+------+
| test |
+------+
| 0 |
+------+
1 row in set, 1 warning (0.00sec)
xxxx.test>show warnings;+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a1' |
+---------+------+-----------------------------------------+
1 row in set (0.00sec)--开头不是字符,一直截取到第一个不是字符的位置
xxxx.test> select cast('1a1' as unsigned int) astest ;+------+
| test |
+------+
| 1 |
+------+
1 row in set, 1 warning (0.00sec)
xxxx.test> select cast('123a1' as unsigned int) astest ;+------+
| test |
+------+
| 123 |
+------+
1 row in set, 1 warning (0.00sec)--直接按照字符截取,补上了20(不能补19)
xxxx.test> select cast('23:12:13' as datetime) astest ;+---------------------+
| test |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00sec)--为什么不能转换为timestamp,没搞清楚,官方文档给的转换类型里没有timestamp。如果是这样的话,上面的datetime就不好解释为什不是1923了。难道是检测了当前的系统时间?
xxxx.test> select cast('23:12:13' as timestamp) astest ;
ERROR1064 (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 'timestamp) as test' at line 1
--这个时间无法转换成datetime
xxxx.test> select cast('10:12:32' as datetime) astest ;+------+
| test |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00sec)
xxxx.test>show warnings ;+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10:12:32' |
+---------+------+--------------------------------------+
1 row in set (0.00sec)--5.5版本下,时间转字符,会增加ms
xxxx.(none)> selectversion();+------------+
| version() |
+------------+
| 5.5.31-log |
+------------+
1 row in set (0.00sec)
xxxx.(none)> select CURTIME(), CURTIME()+0, NOW(), NOW()+0;+-----------+---------------+---------------------+-----------------------+
| CURTIME() | CURTIME()+0 | NOW() | NOW()+0 |
+-----------+---------------+---------------------+-----------------------+
| 15:40:01 | 154001.000000 | 2016-05-06 15:40:01 | 20160506154001.000000 |
+-----------+---------------+---------------------+-----------------------+
1 row in set (0.00sec)--5.6 不会
xxxx.test> selectversion();+------------+
| version() |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00sec)
xxxx.test> select CURTIME(), CURTIME()+0, NOW(), NOW()+0;+-----------+-------------+---------------------+----------------+
| CURTIME() | CURTIME()+0 | NOW() | NOW()+0 |
+-----------+-------------+---------------------+----------------+
| 15:40:55 | 154055 | 2016-05-06 15:40:55 | 20160506154055 |
+-----------+-------------+---------------------+----------------+
1 row in set (0.00 sec)