在MySQL中,在整型字段中查询字符串,返回了所有为0的结果,应该返回null或空。
例如表结构信息:
CREATE TABLE `t` (
`name` char(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
表中数据:
mysql> select id from t;
+----+
| id |
+----+
| 0 |
| 0 |
| 1 |
+----+
查询字符串a,返回了所有是0 的:
mysql> select id from t where id='a';
+----+
| id |
+----+
| 0 |
| 0 |
+----+
警告信息:
mysql> show warnings;
+---------+------+---------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+---------+------+---------------------------------------+
查询字符串'1a',可以返回1:
mysql> select id from t where id='1a';
+----+
| id |
+----+
| 1 |
+----+
报警信息:
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '1a' |
+---------+------+----------------------------------------+
这种情况不经常遇到,解决办法是在程序中进行数据类型的判断,对前端传入的值进行校验
欢迎批评指正
转载于:https://blog.51cto.com/amnesiasun/1931639