mysql日期隐式转换_MySQL之隐式转换，回头有时间了看下

-- 字符开头，直接是0

xxxx.test> select cast('a1' as unsigned int) as test ;

+------+

| test |

+------+

| 0 |

+------+

1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings;

+---------+------+-----------------------------------------+

| Level | Code | Message |

+---------+------+-----------------------------------------+

| Warning | 1292 | Truncated incorrect INTEGER value: 'a1' |

+---------+------+-----------------------------------------+

1 row in set (0.00 sec)

-- 开头不是字符，一直截取到第一个不是字符的位置

xxxx.test> select cast('1a1' as unsigned int) as test ;

+------+

| test |

+------+

| 1 |

+------+

1 row in set, 1 warning (0.00 sec)

xxxx.test> select cast('123a1' as unsigned int) as test ;

+------+

| test |

+------+

| 123 |

+------+

1 row in set, 1 warning (0.00 sec)

-- 直接按照字符截取，补上了20(不能补19)

xxxx.test> select cast('23:12:13' as datetime) as test ;

+---------------------+

| test |

+---------------------+

| 2023-12-13 00:00:00 |

+---------------------+

1 row in set (0.00 sec)

-- 为什么不能转换为timestamp，没搞清楚，官方文档给的转换类型里没有timestamp。如果是这样的话，上面的datetime就不好解释为什不是1923了。难道是检测了当前的系统时间？

xxxx.test> select cast('23:12:13' as timestamp) as test ;

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 'timestamp) as test' at line 1

-- 这个时间无法转换成datetime

xxxx.test> select cast('10:12:32' as datetime) as test ;

+------+

| test |

+------+

| NULL |

+------+

1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings ;

+---------+------+--------------------------------------+

| Level | Code | Message |

+---------+------+--------------------------------------+

| Warning | 1292 | Incorrect datetime value: '10:12:32' |

+---------+------+--------------------------------------+

1 row in set (0.00 sec)

-- 5.5版本下，时间转字符，会增加ms

xxxx.(none)> select version();

+------------+

| version() |

+------------+

| 5.5.31-log |

+------------+

1 row in set (0.00 sec)

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.00 sec)

-- 5.6 不会

xxxx.test> select version();

+------------+

| version() |

+------------+

| 5.6.24-log |

+------------+

1 row in set (0.00 sec)

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)