mysql char 空格_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条件中的左侧空格进行判断，右侧末尾的空格会忽略