MySQL integer field query string, return all results of 0

In MySQL, the query string in an integer field returns all results of 0 and should return null or NULL.

For example, table structure information:

CREATE TABLE ' t ' (' name ' char () COLLATE utf8mb4_unicode_ci default NULL, ' ID ' int (one-by-one) not ' null ' engine=innodb default CHARSET=UTF8MB4 Collate=utf8mb4_unicode_ci

Table of data:

Mysql> Select ID from t;+----+|  ID |+----+|  0 | |  0 | | 1 |+----+

Query string A, which returns all of the 0:

Mysql> Select ID from t where id= ' a '; +----+|  ID |+----+|  0 | | 0 |+----+

Warning message:

Mysql> Show warnings;+---------+------+---------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: ' A ' |+---------+------+---------------------------------------+

Query string ' 1a ', can return 1:

Mysql> Select ID from t where id= ' 1a '; +----+|  ID |+----+| 1 |+----+

Alarm information:

Mysql> Show warnings;+---------+------+----------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------+| Warning | 1292 | Truncated incorrect DOUBLE value: ' 1a ' |+---------+------+----------------------------------------+

This situation is not often encountered, the solution is to do in the program data type of judgment, the front end of the value of the check

Welcome to Criticize

This article is from the "Amnesiasun" blog, make sure to keep this source http://amnesiasun.blog.51cto.com/10965283/1931639

MySQL integer field query string, return all results of 0