MySQL · 2017-02-05 7

MySQL的char和varchar针对空格的处理

MySQL的char和varchar存储和查询中包含空格的实验

MySQL版本

root@localhost : db01 03:58:19>>> select version();
+--------------+
| version()    |
+--------------+
| 5.7.15-9-log |
+--------------+
1 row in set (0.00 sec)

一、测试char包含空格的存储和查询


root@localhost : db01 03:59:34>>> create table test_char(id int,name char(10));
Query OK, 0 rows affected (0.01 sec)

root@localhost : db01 03:59:41>>> insert into test_char values(1,"a"),(2,"  a"),(3,"a  ");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost : db01 04:02:01>>> select concat(id,"'",name,"'") from test_char;
+-------------------------+
| concat(id,"'",name,"'") |
+-------------------------+
| 1'a'                    |
| 2'  a'                  |
| 3'a'                    |
+-------------------------+
3 rows in set (0.00 sec)

测试数据中的空格均为2个空格

测试发现,存储的数据,char数据类型的右侧空格存储的时候被删除了,但是左侧空格还保留。


root@localhost : db01 04:03:53>>> select * from test_char where name="a"; 
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
+------+------+
2 rows in set (0.00 sec)

root@localhost : db01 04:04:00>>> select * from test_char where name="  a";
+------+------+
| id   | name |
+------+------+
|    2 |   a  |
+------+------+
1 row in set (0.00 sec)

root@localhost : db01 04:04:05>>> select * from test_char where name="a  ";
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
+------+------+
2 rows in set (0.00 sec)

测试发现,查询的时候,char数据类型只是会判断查询条件中左侧的空格,右侧的空格也会忽略,因此查询1和查询3的结果是一致的。


二、测试varchar包含空格的存储和查询

root@localhost : db01 04:06:05>>> create table test_varchar(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)

root@localhost : db01 04:06:43>>> insert into test_varchar values(1,"a"),(2,"  a"),(3,"a  ");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@localhost : db01 04:06:51>>> select concat(id,"'",name,"'") from test_varchar;
+-------------------------+
| concat(id,"'",name,"'") |
+-------------------------+
| 1'a'                    |
| 2'  a'                  |
| 3'a  '                  |
+-------------------------+
3 rows in set (0.00 sec)

测试发现,存储的数据,varchar数据类型的空格均保留,没有进行删除。

root@localhost : db01 04:07:00>>> select * from test_varchar where name="a"; 
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
+------+------+
2 rows in set (0.00 sec)

root@localhost : db01 04:07:59>>> select * from test_varchar where name="  a"; 
+------+------+
| id   | name |
+------+------+
|    2 |   a  |
+------+------+
1 row in set (0.00 sec)

root@localhost : db01 04:08:02>>> select * from test_varchar where name="a  ";   
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    3 | a    |
+------+------+
2 rows in set (0.00 sec)

测试发现,查询的时候,varchar数据类型只是会判断查询条件中左侧的空格,会忽略右侧的空格,因此查询1和查询3的结果是一致的。


三、小结

char在存储的时候会将右侧空格进行剔除,保留左侧空格。
varchar在存储的时候保留所有空格,不进行任何删除

varchar和char在查询的时候都只会根据where条件中的左侧空格进行判断,右侧末尾的空格会忽略