MYSQL中的空格及大小写问题

2019独角兽企业重金招聘Python工程师标准>>>

项目去O，有同学反映oracle中的数据导入到mysql中不对了，背景是oracle中的数据存在空格敏感字段，比如oracle中123，空格123,123空格就是3个不同的数据，符合唯一约束。而mysql则认为存在相同的数据

另外对于oracle来说，大小写是敏感的，而mysql则不是，比如ABC，abc在oracle总是两条数据，而在mysql中默认是不符合唯一约束的

就以上两个问题我们分别来看一下，第一是大小写的问题

ORACLE中建表TTT，插入两条大小写的数据

INSERT INTO TTT VALUES (1,'name1');
INSERT INTO TTT VALUES (2,'NAME1');

SELECT DISTINCT("name") from TTT;

结果是:

NAME1
name1

同样的操作在mysql中直接结果如下：

INSERT INTO ttttt VALUES (1,'name1');
INSERT INTO ttttt VALUES (2,'NAME1');

SELECT DISTINCT(`name`) from ttttt;

返回的结果为：

name1

也就是是说mysql默认是大小写不敏感的，下面我来看下mysql中关于大小写敏感的成熟的解决方案：

ALTER TABLE `TTTTT`
MODIFY COLUMN `name`  varchar(64) CHARACTER SET gbk COLLATE gbk_bin NULL DEFAULT NULL AFTER `id`;

再次在mysql中执行

SELECT DISTINCT(`name`) from ttttt;

name1
NAME1

结果OK.所做的操作就是把字符集和排序规则改了一下

第二个问题就是mysql中的空格问题

先在oracle中执行

INSERT INTO TTT VALUES (1,'name1');
INSERT INTO TTT VALUES (2,' name1');
INSERT INTO TTT VALUES (3,'name1 ');
INSERT INTO TTT VALUES (4,' name1 ');

SELECT DISTINCT("name") FROM TTT

所得的结果为：

name1
 name1
 name1 
name1

也就是说oracle中name1，空格name1，空格name1空格，name1空格是4种不同的数据

分别对应下他们的长度：

SELECT "LENGTH"("name") FROM TTT

5
6
6
7

接下来同样的操作在msyql中执行一下：

SELECT DISTINCT(`name`) from ttttt

执行结果如下：

name1
 name1

也就是说mysql会把name1，name1空格合并，空格name1，空格name1空格合并，即在做distinct的时候其实mysql做了一次righttrim，即去掉右边的空格

那么在存储的时候mysql也做了这样的操作么？即只存righttrim后结果？

SELECT LENGTH(`name`) from ttttt

5
6
6
7

从上面的结果来看，空格的长度是包含在其中的。

那么我们可以多做几个实验，distinct的时候mysql有做righttrim，那么查询的时候是否有类似操作呢？另外如果本身是唯一键的时候判断唯一的时候是否也会做righttrim呢？

首先是查询的时候

SELECT * from ttttt where `name` ='name1'

1	name1
3	name1

结果是mysql认为name1和name1空格是一样的

那么唯一约束的时候是否会做同样的操作呢？

ALTER TABLE `TTTTT`
ADD UNIQUE INDEX `uniname` (`name`) ;

清空表再重新插入

INSERT INTO ttttt VALUES (1,'name1');
INSERT INTO ttttt VALUES (2,' name1');
INSERT INTO ttttt VALUES (3,'name1 ');
INSERT INTO ttttt VALUES (4,' name1 ');

[SQL] INSERT INTO ttttt VALUES (1,'name1');
受影响的行: 1
时间: 0.074ms
[SQL] 
INSERT INTO ttttt VALUES (2,' name1');
受影响的行: 1
时间: 0.071ms
[SQL] 
INSERT INTO ttttt VALUES (3,'name1 ');
[Err] 1062 - Duplicate entry 'name1 ' for key 'uniname'

从结果可以看出来，还是会认为后置空格是一样的。

那么这个问题在mysql怎么解决呢？解决方案是在字段前加一个binary关键字

SELECT DISTINCT(BINARY `name`) from ttttt

查询结果如下：

name1
 name1
name1 
 name1

也就是说mysql解决了空格的识别问题，会认为后置空格是不同的数据，这点也很容易理解，因为之前我们看各个字段的length的时候发现了其实各个数据的length还是不一样的

总结一下，mysql中的空格处理及大小写的处理和oralce还是有很大的差别的，对于mysql中的大小写问题我们可以设置排序规则，而mysql的后置空格问题我们可以使用binary关键字来强制检测空格

转载于:https://my.oschina.net/zimingforever/blog/103663