MySQL CHAR和VARCHAR数据类型介绍

太过爱你忘了你带给我的痛 2024-04-18 01:55 11阅读 0赞

CHAR类型是固定长度的，长度范围为0到255。如果存储的值不覆盖字段长度，存储数据的其他的部分会在值的右边自动添加空格。  
  
 VARCHAR字段中的值是可变长度的字符串，长度范围为0到65535。VARCHAR字段的值以1字节或2字节的长度前缀加上数据的方式存储。长度前缀指示了字段存储的字节数，如果一个字段存储的值不超过255字节，它使用一个长度前缀，如果字段存储的值超过了255字节，它使用两个长度前缀。  
  
 如果没有启用严格SQL模式，分配给CHAR或VARCHAR字段超过字段最大长度的值，这个值会被截断保存到数据库中，并会产生一个警告。对于VARCHAR字段来说，不管使用什么样的SQL模式，分配给VARCHAR字段超过字段最大长度的值，在值插入前，超过字段最大长度的部分会被截断，并会产生一个警告。对于CHAR字段来说，不管使用什么样的SQL模式，超过字段最大长度的部分会被截断，并隐式插入到数据库中。  
  
 \--测试①  
  
 MariaDB \[test\]> show variables like '%sql%mode%';  
 \+---------------+--------------------------------------------+  
 | Variable\_name | Value                                      |  
 \+---------------+--------------------------------------------+  
 | sql\_mode      | NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION |  
 \+---------------+--------------------------------------------+  
 1 row in set (0.26 sec)  
  
 MariaDB \[test\]> drop table vc;  
 Query OK, 0 rows affected (0.04 sec)  
  
 MariaDB \[test\]> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));  
 Query OK, 0 rows affected (0.54 sec)  
  
 MariaDB \[test\]> INSERT INTO vc VALUES ('ab  ', 'ab  ');  
 Query OK, 1 row affected (0.14 sec)  
  
 \--查看插入的数据中是否保留空格  
  
 MariaDB \[test\]> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;  
 \+---------------------+---------------------+  
 | CONCAT('(', v, ')') | CONCAT('(', c, ')') |  
 \+---------------------+---------------------+  
 | (ab  )              | (ab)                |  
 \+---------------------+---------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select length(v),length(c) from vc;  
 \+-----------+-----------+  
 | length(v) | length(c) |  
 \+-----------+-----------+  
 |         4 |         2 |  
 \+-----------+-----------+  
 1 row in set (0.00 sec)  
  
 \--插入超过字段最大值的数据到表中  
  
 MariaDB \[test\]> insert into vc values('abcdefg','abcedeg');  
 Query OK, 1 row affected, 2 warnings (0.03 sec)  
  
 MariaDB \[test\]> show warnings;  
 \+---------+------+----------------------------------------+  
 | Level   | Code | Message                                |  
 \+---------+------+----------------------------------------+  
 | Warning | 1265 | Data truncated for column 'v' at row 1 |  
 | Warning | 1265 | Data truncated for column 'c' at row 1 |  
 \+---------+------+----------------------------------------+  
 2 rows in set (0.00 sec)  
  
 MariaDB \[test\]> select \* from vc;  
 \+------+------+  
 | v    | c    |  
 \+------+------+  
 | ab   | ab   |  
 | abcd | abce |  
 \+------+------+  
 2 rows in set (0.00 sec)

\--测试②  
  
 MariaDB \[test\]> CREATE TABLE names (myname CHAR(10));  
 Query OK, 0 rows affected (0.13 sec)  
  
 MariaDB \[test\]> INSERT INTO names VALUES ('Monty');  
 Query OK, 1 row affected (0.00 sec)  
  
 \--查看表中数据在WHERE条件中使用=的条件中带空格的执行情况  
  
 MariaDB \[test\]> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;  
 \+------------------+--------------------+  
 | myname = 'Monty' | myname = 'Monty  ' |  
 \+------------------+--------------------+  
 |                1 |                  1 |  
 \+------------------+--------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names;  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> select length(myname) from names;  
 \+----------------+  
 | length(myname) |  
 \+----------------+  
 |              5 |  
 \+----------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty  ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty   ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty    ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty     ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty      ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname = 'Monty       ';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 \--查看表中的数据在like条件下的执行情况  
  
 MariaDB \[test\]> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;  
 \+---------------------+-----------------------+  
 | myname LIKE 'Monty' | myname LIKE 'Monty  ' |  
 \+---------------------+-----------------------+  
 |                   1 |                     0 |  
 \+---------------------+-----------------------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname like '%Monty%';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname like 'Monty%';  
 \+--------+  
 | myname |  
 \+--------+  
 | Monty  |  
 \+--------+  
 1 row in set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname like 'Monty %';  
 Empty set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname like 'Monty  %';  
 Empty set (0.00 sec)  
  
 MariaDB \[test\]> SELECT \* FROM names where myname like 'Monty   %';  
 Empty set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121119/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121119/