Строковые типы в MySQL

Строковые типы бывают двух классов binary strings и nonbinary strings. Первый класс это массивы байтов, а второй - массивы символов. То есть первый всегда состоит из элементов по 1 байту, и сравнивается просто математически. А второй в зависимости от кодировки может состоять и из двухбайтовых елементов и сравниваться с определенными COLLATION-ами.



NONBINARY STRINGS: CHAR AND VARCHAR
Это сроковые типы MySQL. Когда их определяешь то указываешь в скобках[] размер, какой строка будет занимать.
Максимальный размер для CHAR 255. Максимальные размер -- максимальное количество символов (не байт).

Для VARCHAR - 65,535. Только вот этот масимальный размер может быть в случае, если кодировка однобайтовая, и колонка в таблице одна (под строку в таблицу выделено не больше 65,535 байт). Тоесть если строка в utf8, то максимальное количество символов падает сразу до 65,535/2.

Отличаются эти типы методом хранения и выборки.
CHAR:
1) всегда хранит декларируемый размер байт.
2) если значение вставлено меньше размера, то остальные байты заполняются пробелами(хвостовыми).
3) при выборке из такой колонки хвостовые пробелы не выбираются.
4) Если попытаться сохранить хвостовые пробелы явно, то они перед сохраненим откинуться, при хранении вставятся свои, а результат выборки будет без хвостовых пробелов. Если есть желание получать хвостовые пробелы(но это будут не те, которые вставлялись, а те которые заполнили пустые места) нужно включить в базе строгий режим sql.

VARCHAR:
1) Хранится только те байты, которые вставили, + 1 или 2 байта префикса (1-н если размер определили <=255 и 2-а если >255). Префикс хранит максимальные размер строки (число в квадратных скобках).
2) Если мы сохраняем хвостовые пробелы, то они сохранятся, и они тоже будут в результате выборки, не зависимо от режима sql.


Если мы попытаемся вставить строку превышающую декларированный размер, то в обычном режиме sql она обрежется до максимального размера и сгенерируется предупреждение. В строгом -- не произойдет вставка, а произойдет ошибка.
Если в превышающий размер входят байты пробелов, то они откинутся без предупреждения в любом режиме ескуель.

Для стравнений(collation) хвостовые пробелы не играют никакого значения, сторока без пробелов и точно такая строка с хвостовыми пробелами бутут равными. Точно также и для индексных полей - будет дубликейшин ерор, если попытаться вставить строку(понятно что такое возможно для varchar) которая отличается только хвостовым проблом.

BINARY STRINGS: BINARY AND VARBINARY

Это строковый тип, который содержит байты а не символы. Их размеры тоже <=255 и <=65,535, но при этом это количество байтов, а не символов.

BINARY(3) и CHAR(3) BINARY это не одно и тоже. Второе это CHAR(3) CHARSET latin1 COLLATION latin1_bin (для дефолта latin1). То что тут кодировка совпала размером с бинарным типом ничего не решает, ведь бинарное странение и сравнение _bin будет отличаться. Например последнее, сравнивая большие и маленькие символы, различия видеть не будет, а первое видет только голые цифры.

При вставке в BINARY не полного количества байтов, все остальные заполняться нулевыми (0x00 - \0). А вот кардинальное отличие от своего нонбайнери брата char: выборка получает результат с хвостовыми нулевыми байтами.


VARBINARY будет возвращать точно то, что мы в него положили.

Если размер вставляемого будет превышать обьявленный, то лишнее обрежется, при это выведется варнинг. Если включить строгий режим sql, то произойдет ошибка и ничего вставляться не будет.

ГИГАНТЫ: BLOB и TEXT


BLOB - binary string.
TEXT - nobinary string.

BLOB бывает TINYBLOB, BLOB, MEDIUMBLOB и LONGBLOB.
TEXT бывает TINYTEXT, TEXT, MEDIUMTEXT и LONGTEXT.

Если попытаться сохранить строки превышаеющие размер "гинанта", то как обычно, лишнее удалиться с варнингом. И чтобы этому помешать нужно, как обычно, включить строгий режим. Но это не поможет если мы в TEXT(любых подтипов) будем вставлять строку, а в превышающих символах будут находиться пробельные - они удаляться.

В принципе блоб это тоже самое что варбайнери, а текст -- варчар, но у они практически безразмерные. Все что говорилисось для варов можно применить и для соответсвующего гиганта.

Если мы создаем индекс для BLOB или TEXT, то мы обязательно должны указывать количество байт(символов), которые принимают участие в индексировании, у варах определение этих частей не обязательно.

В гигантах не может быть дефолтовых значений.

Когда делаешь выборку, то выбирать гигантов нужно только в том случаее, если именно они нужны, в других - это не продуктивно. Результаты селекста хранятся не в оперативной памяти, а на диске во временных таблицах - движок MEMORY не поддерживает такие типы.

Сортировка гинантов или групировка происходит не по всем их потенциально бесконечным байтам, а по количеству max_sort_length. По умолчанию это 1024 байта. Это значение каждый может менять для своей сессии, на случай если такого количества байт маловато.

mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment; 

Эту же задачу можно рещить и таким способом:

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM t
    -> ORDER BY SUBSTRING(comment,1,2000);

Чтобы получить гиганта мы может столкнуться с проблемой размера буффера потока сервера на оттдачу(net_buffer_length), когда он превышается то он может быть расширен до размера max_allowed_packet (по умолчанию он 1Мб, может быть увеличен до 1Гб, при выборе размера нужно учитывать что значени должно быть в байтах и кратным 1024). Изменить это значение на сервере может быть не достаточно, клиент тоже должен быть готовым расширить свой буффер до размеров идущих к нему байт. Такие клиентские программы как mysql и mysqldump имеют ключь max_allowed_packet, кстати он поумолчанию 1Гб(тоесть ориентирован на максимум сервера). На сервере max_allowed_packet нельзя ставить для сессии.

Гиганты - это единственные типы, которые храняться отдельным обьектом от своей строки, все остальные типы храняться каждый в своей колонке.

ENUM

Это стоковой тип, только строки могут быть из предоперделенного перечисления строк.

При создании енам все предопределенные строки должны быть в литералах:

CREATE TABLE sizes (
    name ENUM('small', 'medium', 'large')
);

Не кактит:

CREATE TABLE sizes (
    c1 ENUM('small', CONCAT('med','ium'), 'large')
);

и это тоже

SET @mysize = 'medium';

CREATE TABLE sizes (
    name ENUM('small', @mysize, 'large')
);

Это перечисление имеет индекс - то есть каждая строка имеет свой номер, и мы можем указать не строковое значение а индексное(тут индекс имеется ввиду как номер в массиве).

Если мы попытаемся вставить строку которой нет в перечислении, то вставится пустая строка, пустая строка всегда имеет индекс 0. Предыдущее возможно, если не включен строгий режим sql. Если эта колонка опеределена на то, что она может хранить налл, то налл присваивается поумолчанию. У нала индрекс тоже налл.

CREATE TABLE table (
    col ENUM('one', 'two',         'three') NULL
); 

Value Index  
 NULL    NULL
  ''     0  
'one'      1
'two'      2  
'three'   3

Крайне не хорошо в перечислении иметь цифры вместо строк-букв. Потому что:
Во первых, забудем взять в литералы вставит по индексу, а не то что мы хотели.
Если попытаемся в литералах вставить цифру, которая не определена в перечислении, то строка преобразуется в число, и попытается вставить по такому индексу. Примеры

numbers ENUM('0','1','2')

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

По поводу сортировки и групировки. Порядок определен индексами, если нам нужно отсортировать по значениям нужно поступать так:
 ORDER BY CAST(col AS CHAR)
 ORDER BY CONCAT(col)

Если есть желание выбрать индекс, а не значение, то тогда так:

mysql> SELECT enum_col+0 FROM tbl_name;

Максимально возможное количетсво елементов в перечислени 65,535. И понятное дело, что у такого типа есть кодировка и коллейшин.

SET

Это множество. То есть определяя такую колонку мы определяем для нее ряд возможныз значений. И в ней может хранится ничего, одно из значений, несколько таких значений, все значения.

Вставляя множество в эту колонку, мы должны елементы перечислить через запятую, порядок не важен, все равно в результате они окажутся в том порядке в котором были определены при создании колонки. При дублировании и указании несуществуюшего элемента множества мы увидим предупреждение, но опереция вставки произойдет.


Примеры:

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
 

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

Колонка типа множества имеет строковое представление, числовое и в виде битовой маски.
 SET('a','b','c','d')

SET Member	Decimal Value	Binary Value
'a'	1	0001
'b'	2	0010
'c'	4	0100
'd'	8	1000

То есть вставляя в такую колонку 9(1001), мы вставляем 'a','d'.

Сравнение делается так:

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

Но со вторым вариантом нужно быть внимательным, ведь он тоже выберет строки, где будет присутствовать елемент, для которого это будет подстрокой.

mysql> SELECT * FROM tbl_name WHERE set_col & 9;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

Первое выражение выберет те строки, у которых содержится 1 и 4 элемент(но могут и другие).
А второй опять требует внимания, мы пытаеся выбрать строго только тот и тот, но нужно их указать в том порядке, в котором создавалась колонка.
Чтобы увидеть порядок нужно

SHOW COLUMNS FROM tbl_name LIKE set_col

или если колонок в таблице не много
 DESC tbl_name