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)
可以查询出结果,但是会影响索引的使用,索引无效,产生隐式转换。