MySQL学习之弱类型
这篇文章主要讨论一下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', PRIMARY KEY (`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非常相似:
<?php echo 1 + '2a'; //结果输出为3,这里会有一个警告出现
再来插入一条数据:
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 | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set mysql> 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字段中以数字开头的记录全部被删除了,这也是在执行的过程中发生了隐式的数据类型转换。