1,建表语句

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(16) NOT NULL,
  `sex` int(11) NOT NULL,
  `user_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ux_name` (`name`) USING BTREE,
  KEY `uid` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

1,对于int型

mysql> SELECT * FROM `user` where user_id='110';
+----+-------+-----+---------+
| id | name  | sex | user_id |
+----+-------+-----+---------+
|  1 | AAAAA |   0 |     110 |
+----+-------+-----+---------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM `user` where user_id='110';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | ref  | uid           | uid  | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

int型,即使类型不一样,添加了单引号,不影响索引的使用和查询,不产生隐式转换。

如果以数字开头,只取数字开头的,字符串会被截取的,以字母开头的,查询不到

mysql> SELECT * FROM `user` where user_id='110';
+----+-------+-----+---------+
| id | name  | sex | user_id |
+----+-------+-----+---------+
|  1 | AAAAA |   0 |     110 |
+----+-------+-----+---------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM `user` where user_id='110';
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
|  1 | SIMPLE      | user  | ref  | uid           | uid  | 4       | const |    1 |       |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM `user` where user_id='aaaa110';
Empty set, 1 warning (0.01 sec)

2,对varchar型

正常操作,没有问题

mysql> SELECT * FROM `user` where name='1234';
+----+------+-----+---------+
| id | name | sex | user_id |
+----+------+-----+---------+
|  7 | 1234 |   1 |     116 |
+----+------+-----+---------+
1 row in set (0.01 sec)

mysql> explain SELECT * FROM `user` where name='1234';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | user  | ref  | ux_name       | ux_name | 50      | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------------+

如果没有按照指定字段类型查询(添加单引号),如下

mysql> SELECT * FROM `user` where name=1234;
+----+------+-----+---------+
| id | name | sex | user_id |
+----+------+-----+---------+
|  7 | 1234 |   1 |     116 |
+----+------+-----+---------+
1 row in set, 4 warnings (0.00 sec)

mysql> explain SELECT * FROM `user` where name=1234;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | ux_name       | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

可以查询出结果,但是会影响索引的使用,索引无效,产生隐式转换。