Предисловие:
Ранее я писал статью о типе int, и мне всегда хотелось написать статью о типе поля string. В последнее время я стал уделять больше внимания статьям в этой области, и решил закончить затянувшуюся статью. В этой статье в основном будут представлены использование и различия между строковыми типами char и varchar.
Экспериментальная среда в этой статье — MySQL 5.7.23, механизм хранения — Innodb, sql_mode использует строгий режим, а набор символов — utf8.
▍1. Знакомство с типом CHAR
Когда мы обычно используем тип char для определения поля, мы часто указываем его длину M, то есть char(M). На самом деле M относится к количеству символов, то есть максимальному количеству символов, которое может быть сохранено в этом поле. M может быть не указано. Значение по умолчанию — 1, диапазон — [0,255]. Одна буква, число , китайский и т.д. все занимают один символ. Следующий китайский символ в наборе символов utf8 занимает 3 байта. Ниже мы просто тестируем:
# 假设以如下建表语句创建测试表
CREATE TABLE `char_tb1` (
`col1` char DEFAULT NULL,
`col2` char(5) DEFAULT NULL,
`col3` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 进入数据库查询建表语句如下 发现char(M) M可不指定，默认为1
mysql> show create table char_tb1\G
*************************** 1. row ***************************
Table: char_tb1
Create Table: CREATE TABLE `char_tb1` (
`col1` char(1) DEFAULT NULL,
`col2` char(5) DEFAULT NULL,
`col3` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 插入数据 可以看出M表示保存的最大字符数，字母、数字、中文等都是占用一个字符
mysql> insert into char_tb1 (col1) values ('a'),('1'),('王'),(']');
Query OK, 4 rows affected (0.01 sec)
mysql> insert into char_tb1 (col1) values ('aa'),('12');
ERROR 1406 (22001): Data too long for column 'col1' at row 1
mysql> select * from char_tb1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a | NULL | NULL |
| 1 | NULL | NULL |
| 王 | NULL | NULL |
| ] | NULL | NULL |
+------+------+------+
4 rows in set (0.00 sec)
mysql> insert into char_tb1 (col2) values ('abcd'),('王-123'),('^*123'),('12'),('一二三四五');
Query OK, 5 rows affected (0.01 sec)
mysql> insert into char_tb1 (col2) values ('abcdef');
ERROR 1406 (22001): Data too long for column 'col2' at row 1
mysql> select * from char_tb1;
+------+-----------------+------+
| col1 | col2 | col3 |
+------+-----------------+------+
| a | NULL | NULL |
| 1 | NULL | NULL |
| 王 | NULL | NULL |
| ] | NULL | NULL |
| NULL | abcd | NULL |
| NULL | 王-123 | NULL |
| NULL | ^*123 | NULL |
| NULL | 12 | NULL |
| NULL | 一二三四五 | NULL |
+------+-----------------+------+
9 rows in set (0.00 sec)
# 下面测试发现M的范围是[0,255]
mysql> alter table char_tb1 add column col4 char(0);
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table char_tb1 add column col5 char(255);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table char_tb1 add column col5 char(256);
ERROR 1074 (42000): Column length too big for column 'col5' (max = 255); use BLOB or TEXT instead▍2.Введение типа VARCHAR
Точно так же M в varchar(M) представляет максимальное количество сохраненных символов, а одна буква, цифра, китайский язык и т. д. занимают один символ. varchar может хранить длину от 0 до 65535 байт, кроме того, varchar необходимо использовать 1 или 2 дополнительных байта для записи длины строки: если максимальная длина столбца меньше или равна 255 байтам, только 1 байт используется представление, в противном случае используются 2 байта. Для движка Innodb для набора символов utf8 один китайский символ занимает 3 байта, поэтому максимальное значение M в varchar(M) не будет превышать 21845, то есть диапазон M равен [0, 21845), и M должен быть указано. Кроме того, MySQL требует, чтобы длина одного поля не превышала 65 535 байт, максимальное ограничение на одну строку — 65 535, исключая поля TEXT и BLOB. То есть сумма длин, определяемых всеми полями varchar в одной таблице, не может быть больше 65 535, поэтому не все M в полях varchar(M) можно взять равными 21 844. Давайте проверим это:
# 假设以如下建表语句创建测试表
CREATE TABLE `varchar_tb1` (
`col1` varchar(0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 查看建表语句 增加字段 发现M必须指定
mysql> show create table varchar_tb1\G
*************************** 1. row ***************************
Table: varchar_tb1
Create Table: CREATE TABLE `varchar_tb1` (
`col1` varchar(0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table varchar_tb1 add column col2 varchar;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
# 下面测试证明M最大可取到21844
mysql> CREATE TABLE `varchar_tb2` (col1 varchar(21844));
Query OK, 0 rows affected (0.04 sec)
mysql> CREATE TABLE `varchar_tb3` (col1 varchar(218445));
ERROR 1074 (42000): Column length too big for column 'col1' (max = 21845); use BLOB or TEXT instead
# 下面测试证明单行最大限制为65535字节
mysql> CREATE TABLE `varchar_tb3` (col1 varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> alter table varchar_tb3 add column col2 varchar(21844);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> alter table varchar_tb3 add column col2 varchar(21834);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> alter table varchar_tb3 add column col2 varchar(21833);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table varchar_tb3\G
*************************** 1. row ***************************
Table: varchar_tb3
Create Table: CREATE TABLE `varchar_tb3` (
`col1` varchar(10) DEFAULT NULL,
`col2` varchar(21833) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)▍3. Сравнение CHAR и VARCHAR
Тип CHAR имеет фиксированную длину, и MySQL всегда выделяет достаточно места в соответствии с определенной длиной строки. Когда значения CHAR сохраняются, они дополняются пробелами справа до указанной длины, а конечные пробелы удаляются при извлечении значений CHAR.
Тип VARCHAR используется для хранения строк переменной длины, при сохранении, если символы не достигают заданного количества цифр, после них не добавляются пробелы. Однако, поскольку строки становятся длиннее, UPDATE может сделать строки длиннее, чем раньше, что приводит к дополнительной работе. Если пространство, занимаемое строкой, увеличивается, и на странице больше нет места для хранения, в этом случае InnoDB необходимо разделить страницу, чтобы строка поместилась на странице, что увеличивает фрагментацию.
Ниже кратко описаны применимые сценарии типов полей CHAR и VARCHAR:
CHAR подходит для хранения очень коротких строк или всех значений, близких к одинаковой длине. Например, CHAR отлично подходит для хранения значения MD5 пароля, поскольку это значение фиксированной длины. Для часто изменяющихся данных CHAR также лучше, чем VARCHAR, потому что тип CHAR фиксированной длины не подвержен фрагментации. Для очень коротких столбцов CHAR также более эффективен в хранении, чем VARCHAR. Например, при использовании CHAR(1) для хранения только значений Y и N требуется только один байт при использовании однобайтового набора символов, а для VARCHAR(1) требуется два байта, поскольку для длины записи имеется дополнительный байт.
Использование VARCHAR уместно в следующих ситуациях: строка очень длинная или сохраняемая строка сильно различается; максимальная длина столбца строки намного больше средней длины; столбец обновляется нечасто, поэтому фрагментация невозможна. вопрос.
В качестве дополнительного примечания, мы должны выделить по мере необходимости при определении максимальной длины поля и сделать оценку заранее. Специально для полей VARCHAR некоторые люди думают, что длина типа данных VARCHAR выделяется в соответствии с фактическими потребностями, и лучше дать ему больший размер. Но это не так.Например, необходимо хранить адресную информацию.Согласно оценке, пока используется 100 символов, мы можем использовать VARCHAR(100) или VARCHAR(200) для их хранения, хотя они используются для хранения символов 90. Данные, объем их хранения одинаков, но потребление памяти разное. Более длинные столбцы потребляют больше памяти, потому что MySQL обычно выделяет блоки памяти фиксированного размера для хранения внутренних значений, что особенно плохо при использовании временных таблиц в памяти для массивов или операций. Таким образом, мы все еще не можем быть слишком щедрыми при назначении типа данных VARCHAR. Снова оцените фактическую требуемую длину и выберите самое длинное поле для установки длины символа. Если вы хотите учесть избыточность, вы можете оставить около 10% длины символа. Никогда не думайте, что VARCHAR выделяет место для хранения в соответствии с фактической длиной и выделяет длину по желанию или просто использует максимальную длину символа.
Суммировать:
В этой статье рассказывается об использовании типов полей CHAR и VARCHAR соответственно, а также дается сравнение и применимые сценарии этих двух типов. В реальной производственной ситуации необходимо подробно проанализировать конкретную ситуацию, и лучше всего выбрать подходящую.Я надеюсь, что эта статья может дать вам ссылку.