MySQL database: quotation marks (bitsCN.com) that cannot be omitted in SQL statements
MySQL database: quotation marks that cannot be omitted in SQL statements
One time I found a BUG in the code and almost fainted during the troubleshooting process. as a DBA, I also encountered such a low-level error. I laughed at myself. In fact, this issue is clearly written in the official manual; (see the importance of the official document)
mysql > create table h1 (id int , col1 char ( 1 ));Query OK, 0 rows affected ( 0.00 sec)mysql > insert into h1 select 1 ,’I ' ;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into h1 select 2,’F ' ;Query OK, 1 row affected ( 0.00 sec)Records: 1 Duplicates: 0 Warnings: 0 mysql > insert into h1 select 2 ,’ 0 ′;Query OK, 1 row affected ( 0.00 sec)Records: 1 Duplicates: 0 Warnings: 0mysql > select * from h1 ;+ —— + —— + | id | col1 | + —— + —— + | 1 | I | | 2 | F | | 2 | 0 | + —— + —— + 3 rows in set ( 0.00 sec)mysql > select * from h1 where col1 = 0 ;+ —— + —— + | id | col1 | + —— + —— + | 1 | I | | 2 | F | | 2 | 0 | + —— + —— + 3 rows in set , 2 warnings ( 0.00 sec)mysql > select * from h1 where col1 = ’ 0 ′;+ —— + —— + | id | col1 | + —— + —— + | 2 | 0 | + —— + —— + 1 row in set ( 0.00 sec)mysql > select * from h1 where col1 = ’ 1 ′;Empty set ( 0.00 sec)mysql > select * from h1 where col1 = 1 ;Empty set , 2 warnings ( 0.00 sec)
No:
Col1 = 0; all rows are selected;
Col1 = '0'; the result set is normal.
mysql > select * from h1 where col1 = ’ 1 ′;Empty set ( 0.00 sec)mysql > select * from h1 where col1 = 1 ;Empty set , 2 warnings ( 0.00 sec)
Want to know why?
Here.
mysql > show warnings;+ ——— + —— + ————————————— + | Level | Code | Message | + ——— + —— + ————————————— + | Warning | 1292 | Truncated incorrect DOUBLE value: ‘I’ | | Warning | 1292 | Truncated incorrect DOUBLE value: ‘F’ | + ——— + —— + ————————————— + 2 rows in set ( 0.00 sec)
When the data type of MySQL does not match, the user input is respected. The field type is implicitly converted and then matched. when the character is converted to a number, the result is 0;
BitsCN.com