这篇文章主要讨论一下MySQL中弱类型可能导致的一些问题
首先创建一个表:
CREATE TABLE `user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`nick` varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT '',`score` int(10) NOT NULL DEFAULT '0',PRIMARYKEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据:
INSERT INTO `user` (`nick`,`score`) VALUES ('张三', '100');
INSERT INTO `user` (`nick`,`score`) VALUES ('1张三', '99分');
INSERT INTO `user` (`nick`,`score`) VALUES ('2张三', '分数');
INSERT INTO `user` (`nick`,`score`) VALUES ('3张三', 97);
在score列我们尝试了多种类型,有数字有汉字,数字有带引号的,也有不带引号的,但无一例外,它们都执行成功了,看下结果:
mysql> select *from user;+----+-------+-------+
| id | nick | score |
+----+-------+-------+
| 1 | 张三 | 100 |
| 2 | 1张三 | 99 |
| 3 | 2张三 | 0 |
| 4 | 3张三 | 97 |
+----+-------+-------+
4 rows in set
在第二行和第三行发生了隐式的数据转换,这点和PHP非常相似:
再来插入一条数据:
INSERT INTO `user` (`nick`,`score`) VALUES (1, '95');
同样成功了,所以:在插入MySQL数据的时候,字符型数据和整数型数据可以发生隐式的转换。(其他数据类型暂时不做测试)
这种数据转换往往带给我们很多困扰,如下:
1、查找时忘加引号导致结果和想要的结果不同。
比如我们想要查找nick为1的结果,我们忽略了字段类型,结果写出了下面的SQL:
mysql> select * from user where nick=1;+----+-------+-------+
| id | nick | score |
+----+-------+-------+
| 2 | 1张三 | 99 |
| 5 | 1 | 95 |
+----+-------+-------+
2 rows in set
这和我们想要的结果就不一样,这样写还有一个后果,我们来看一下,把nick设置为索引:
ALTER TABLE `user` ADD INDEX `nikc` (`nick`) USING BTREE ;
用explain来看一下查询效果:
mysql> explain select * from user where nick=1;+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user | ALL | nikc | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1row in setmysql> explain select * from user where nick='1';+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| 1 | SIMPLE | user | ref | nikc | nikc | 152 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set
如果字段类型是varchar,而查找条件为整数,就会全表查找,不会用到索引。
2、删除数据时如果把=写成了-会造成严重的后果
比如我想删除张三这条记录:
delete from user where nick="张三";
由于手残,我们把=号打成了-号
mysql> delete from user where nick-"张三";
Query OK, 4 rows affected
发生了什么情况?4条语句被删除了,我们查看一下:
mysql> select *from user;+----+------+-------+
| id | nick | score |
+----+------+-------+
| 1 | 张三 | 100 |
+----+------+-------+
1 row in set
居然只剩下一个我们真正想要删除的张三,分析我们的数据,发现了一条规律,nick字段中以数字开头的记录全部被删除了,这也是在执行的过程中发生了隐式的数据类型转换。