mysql - 数据库查询字段类型问题

Question

$q=2;
①  $sql="select * from user where id ='".$q."'";
②  $sql="select * from user where id ='$q'";
③  $sql="select * from user where id =$q";

以上三条语句执行效果是一样的
①②里面$q是字符串型的吧
③的$q是整型
我这样理解不知道对不对

user表里的id字段设置的是int
为何查询的时候是字符串型的也能查出来呢

菜鸟在此谢过

Answer 1

This will not only cause problems as a primary key, but also as other index keys. Int type index field, add "" when querying, will not cause the index not to be used in querying,
However, if the character is changed to a type field and " is not added to the query, the index will not be applicable during the query,
And another point is that without this single quote, the query of Innodb's storage engine table will be upgraded to table-level locking because there is no index

Find an article introducing this:
http://www.zendstudio.net/archives/single-quotes-or-no-single-quotes-in-sql-query/

@江湖大 shrimp’s answer is that when inserting data, there is a problem with the data type. This is also related to MySQL’s strict mode. In strict mode, the syntax requirements are stricter, and then it is no longer a warning.

In the query condition, the main consideration is whether it affects the use of indexes. The quotation above clearly states that it does not matter for int, but there is a difference for char.

Note: There is no test whether an error will be reported if the 严格模式 field value in the query statement under int is added or not with single quotes.

Answer 2

I will update the answer according to @R.ming. What I want to express is that mysql will automatically perform type conversion when the types are different. This is not only done when data is inserted, but also when data is updated, queried and deleted.

Old rules, here are examples. (The emphasis is on the last choice)

mysql> create table temp(a varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp  values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp  values('1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp where a = 1;
+------+
| a    |
+------+
| 1    |
+------+
1 row in set, 1 warning (0.01 sec)

mysql> select * from temp where a = 0;
+------+
| a    |
+------+
| a    |
+------+
1 row in set, 1 warning (0.00 sec)

---

Objection to Young Master’s answer, this has nothing to do with PHP. For PHP, these three SQL statements are just ordinary strings. The original poster’s problem is due to the compatibility of mysql. For fields of int type, if you pass in a string (whether in select, insert or update), it will try to be converted into a number (similar to intval in PHP). For details, please see the example posted below.

In addition, if this type incompatibility is used on a database such as postgresql, an error will be thrown.

p.s. Due to this characteristic of mysql, it is generally recommended to add single quotes to reduce the risk of sql injection, regardless of whether it is a string type or not. ← It doesn’t mean that adding single quotes makes it impossible to inject, it just reduces it.
p.s. I recommend prepared statement

mysql> create table temp (k int);
Query OK, 0 rows affected (0.02 sec)

mysql> desc temp;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| k     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.04 sec)

mysql> insert into temp values(1),(2),(3),('a');
Query OK, 4 rows affected, 1 warning (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> select * from temp;
+------+
| k    |
+------+
|    1 |
|    2 |
|    3 |
|    0 |
+------+
4 rows in set (0.00 sec)

mysql> update temp set k = 'a' where k = 1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> update temp set k = '20' where k = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update temp set k = '30a' where k = 3;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from temp;
+------+
| k    |
+------+
|    0 |
|   20 |
|   30 |
|    0 |
+------+
4 rows in set (0.00 sec)

Answer 3

Database reading in PHP does not distinguish between field types. This seems to have been criticized by many experts.

Answer 4

Directly executing the following two statements under mysql has the same effect. This is due to the database and has nothing to do with PHP, although PHP itself is weakly typed.
select * from user where id ='1';
select * from user where id =1;