The relationship between mysql field type and query statement data type

Experiment

The following table is listed according to the field types stored in the database and the data types in the query statement:

Result

Material list:

	+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| key       | varchar(50)      | NO   | MUL |         |                |
| name      | varchar(100)     | NO   |     |         |                |
| key_value | int(10) unsigned | NO   |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+
| id | key    | name      | key_value |
+----+--------+-----------+-----------+
|  2 | ddd    | zhanghua  |         3 |
|  6 | dsssss | mary      |         9 |
|  8 | dsssss | mary      |         0 |
|  9 | dsll   | Wang Hua      |         0 |
| 10 | 7      | James    |       987 |
| 11 | 008    | Children      |        13 |
| 12 | 78uu   | zhanglei  |        13 |
| 13 | ytyy98 | fengxian  |       131 |
| 14 | y98ds  | lubu      |         0 |
| 15 | zore   | realfirst |         0 |
+----+--------+-----------+-----------+

Result
where to query

1. sting vs string

    select * from role where(`key`='8');
    //No result can be queried. Only when the value is' 008 'can the result be queried
    +----+-----+--------+
   | id | key | name   |
   +----+-----+--------+
   | 11 | 008 | Children   |
   +----+-----+--------+
   

2. sting to int
   When the data type in the where clause is int, mysql will automatically convert the row data to int for comparison.

   select * from role where (`key`=78);
   +----+------+----------+
   | id | key  | name     |
   +----+------+----------+
   | 12 | 78uu | zhanglei |
   +----+------+----------+
   
    select * from role where (`key`=007);
    +----+-----+-----------+
   | id | key | name      |
   +----+-----+-----------+
   | 10 | 7   | James    |
   +----+-----+-----------+
   
    select * from role where (`key`=0);
    +----+--------+----------+
   | id | key    | name     |
   +----+--------+----------+
   |  2 | ddd    | zhanghua |
   |  6 | dsssss | mary     |
   |  8 | dsssss | mary     |
   |  9 | dsll   | Wang Hua     |
   | 13 | ytyy98 | fengxian |
   | 14 | y98ds  | lubu     |
   +----+--------+----------+
   

3. int to sting
   When the field in the database is of type int, the data in the query statement will be automatically converted to type int for query. For example, key_value = "cscd" will be automatically converted to key_value = 0, and key_value ='13csdc 'will be converted to key_value=13.

   select * from role where(key_value='13csdc');
   +----+------+----------+-----------+
   | id | key  | name     | key_value |
   +----+------+----------+-----------+
   | 11 | 008  | Children     |        13 |
   | 12 | 78uu | zhanglei |        13 |
   +----+------+----------+-----------+
   
    select * from role where(key_value='kjhkj');
    +----+--------+-----------+-----------+
   | id | key    | name      | key_value |
   +----+--------+-----------+-----------+
   |  8 | dsssss | mary      |         0 |
   |  9 | dsll   | Wang Hua      |         0 |
   | 14 | y98ds  | lubu      |         0 |
   | 15 | zore   | realfirst |         0 |
   +----+--------+-----------+-----------+
   

4. Int to int
   Not tired;

summary

1. When the data type in the query statement is int, the field value in the database will be automatically converted to int type for analogy.
2. When the field data type in the database is int type, the constraint value in the query statement will be automatically converted to int type for query.