~~178~~ 0 24-01-2022, 22:09

Какие типы ячеек выбрать в БД MySQL?

Какие типы ячеек и для чего лучше в Базе данных MySQL?

По состоянию на 2022 года самая популярная и быстрая кодировка - utf8mb4_0900_ai_ci.

utf8 - обозначает кодировку;
mb4 - обозначает версию или сколько байт используется в обработке данных для одного символа. Если не указано, то обычно подразумевается mb3;
0900 - обозначает версию алгоритма сопоставления Unicode (UCA), на которой базируется сопоставление. Если не указано, то обычно подразумевается версия 4.0.0;
ai - обозначает нечувствительность к диакритическим знакам (например, древнегреческие ᾱ, ᾰ). Если не указано, подразумевается ai или as в зависимости от следующей части в имени сравнения, то есть ai для ci и as для cs;
ci - обозначает нечувствительность к регистру, означает, что не будет разницы между строчными и заглавными символами в запросах к БД. Существуют также версии cs, которые являются чувствительными к регистру.

Существуют два типа чисел: целые и вещественные(числа с дробной частью).

Для хранения целых чисел используйте один из целочисленных типов: TINYINT, SMALLINT. MEDIUMINT, INT или BIGINT, Для хранения они требуют 8,16,24,32 и 64 бита соответственно.

Целые типы данных могут иметь необязательный атрибут UNSIGNED, запрещающий отрицательные значения и приблизительно вдвое увеличивающий верхний предел хранимых положительных значений. Например, тип TINYINT UNSIGNED позволяет хранить значения от 0 до 255, а не от -128 до 127, но минимальным значением здесь будет 0.

TINYINT: от -128 до +127 (макс. 255 если unsigned)
SMALLINT: от -32768 до +32767 (макс. 65 535 если unsigned)
MEDIUMINT: от -8388608 до +8388607 (макс. 16777215 если unsigned)
INT: от -2147486648 до +2147483647 (макс. 4294967295 если unsigned)
BIGINT: 64 бит, максимум 18 x 1018 ...

подробнее смотри в таблицу ниже

VARCHAR подходит в следующих ситуациях:

Максимальная длина строкового столбца намного больше средней длины;
Обновление столбцов происходит редко, поэтому фрагментация не проблема;
Используется сложный набор символов, такой как UTF-8, и каждый символ хранится с разным количеством байтов.
Лучшая стратегия - выделять только то место, которое действительно необходимо, но не слишком много, потому что более длинные столбцы потребляют больше памяти.

Тип CHAR полезен, когда требуется сохранять очень короткие строки или все значения имеют приблизительно одинаковую длину. Например, CHAR хорошо подходит для хранения МD5-сверток паролей пользователей, которые всегда имеют одинаковую длину. Также тип CHAR имеет преимущество над VARCHAR при часто меняющихся данных, поскольку строка фиксированной длины не подвержена фрагментации. Если в столбцах очень короткие строки, тип CHAR также эффективнее, чем VARCHAR: если тип CHAR(1) применяется для хранения значений только Y и N, то в однобайтовой кодировке он займет лишь 1 байт, тогда как для типа VARCHAR(1) из-за наличия дополнительного байта длины строки потребуется 2 байта.

Если значения в текстовой колонке похожи по длине, выбирайте CHAR, иначе — VARCHAR.
!!! Для данных, которые часто меняются, CHAR также лучше, чем VARCHAR

В какой ячейке лучше хранить большие числа?

Mysql, больших точных чисел, предлагает использовать тип DECIMAL: но поскольку процессоры не поддерживают математических операций с подобными числами, Mysql все подсчеты делает на своей стороне. А значит, это очень медленно.

Используйте FLOAT / DOUBLE вместо DECIMAL, если вам не нужны очень точные числа

FLOAT / DOUBLE . В отличие от DECIMAL, тип FLOAT является приблизительным (хранит неточное число). В то же время, процессор умеет работать с этим типом напрямую. Кроме этого, FLOAT занимает меньше места, чем DECIMAL для хранения таких же величин.

ENUM - для фиксированых списков

Позволяет задать определенный список значений и использовать только их в строках таблицы. Например, список стран

ENUM - записывает номер значения вместо самого значения в каждую строку, а это огромная экономия места.

Нельзя использовать ENUM для динамических значений.

Разница между DATETIME и TIMESTAMP

TIMESTAMP - (ставим для дат и событий: дата создания поста, дата комментария) занимает 4 байта и позволяет хранить даты с 1970 года до 2038 года. Значение в данном столбце автоматически обновляются при выполнение операторов INSERT и UPDATE

DATETIME занимает 8 байт и позволяет хранить даты с 1001 года до 9999 года.
Так в чем же принципиальная разница между DATETIME и TIMESTAMP? А состоит она в том что TIMESTAMP показывает дату в зависимости от часового пояса и меньше занимает места.

DATETIME - использовать только когда нужна точная фиксированная дата и она не будет меняться.

DATE - ггг-мм-дд
TIME - чч:мм:сс
DATETIME - гггг-мм-дд чч:мм:сс
TIMESTAMP - гггг-мм-дд чч:мм:сс

Где хранить текст?

Следует помнить что TEXT поля, в отличие от VARCHAR хранятся отдельно от данных таблицы и в целом обрабатываются медленнее.

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

Не используйте TEXT/BLOB типы для сортировки

Если все же нужно их сортировать, то нужно указать длину колонки для индексации, и создать вспомогательную колонку для хранения md5 хэша от текста.

СТРОКИ предоставляют собой массивы символов, и используют их если VARCHAR уже не достаточно.

TINYTEXT - до 255 символов
TEXT - до 65535 символов
MEDIUMTEXT - до 16777215 символов
LONGTEXT - до 4294967295 символов

БИНАРНЫЕ ДАННЫЕ - тоже что и строки только при поиске в них учитывается регистр символов.

TINYBLOB - до 255 символов
BLOB - до 65535 символов
MEDIUMBLOB - до 16777215 символов
LONGBLOB - до 4294967295 символов

BLOB - данные как поместили в базу так они и извлекаются в неизменном виде!!!

Числовые типы данных

Тип данных	Объем памяти	Диапазон	Описание
TINYINT (M)	1 байт	от -128 до 127 или от 0 до 255	Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М - количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями. Примеры: TINYINT - хранит любое число в диапазоне от -128 до 127. TINYINT UNSIGNED - хранит любое число в диапазоне от 0 до 255. TINYINT (2) - предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные. TINYINT (3) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 002.
SMALLINT (M)	2 байта	от -32768 до 32767 или от 0 до 65535	Аналогично предыдущему, но с большим диапазоном. Примеры: SMALLINT - хранит любое число в диапазоне от -32768 до 32767. SMALLINT UNSIGNED - хранит любое число в диапазоне от 0 до 65535. SMALLINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные. SMALLINT (4) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0002.
MEDIUMINT (M)	3 байта	от -8388608 до 8388608 или от 0 до 16777215	Аналогично предыдущему, но с большим диапазоном. Примеры: MEDIUMINT - хранит любое число в диапазоне от -8388608 до 8388608. MEDIUMINT UNSIGNED - хранит любое число в диапазоне от 0 до 16777215. MEDIUMINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные. MEDIUMINT (5) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002.
INT (M) или INTEGER (M)	4 байта	от -2147683648 до 2147683648 или от 0 до 4294967295	Аналогично предыдущему, но с большим диапазоном. Примеры: INT - хранит любое число в диапазоне от -2147683648 до 2147683648. INT UNSIGNED - хранит любое число в диапазоне от 0 до 4294967295. INT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные. INT (5) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 00002.
BIGINT (M)	8 байта	от -2⁶³ до 2⁶³-1 или от 0 до 2⁶⁴	Аналогично предыдущему, но с большим диапазоном. Примеры: BIGINT - хранит любое число в диапазоне от -2⁶³ до 2⁶³-1. BIGINT UNSIGNED - хранит любое число в диапазоне от 0 до 2⁶⁴. BIGINT (4) - предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные. BIGINT (7) ZEROFILL - свободные позиции слева заполнит нулями. Например, величина 2 будет отображаться, как 0000002.
BOOL или BOOLEAN	1 байт	либо 0, либо 1	Булево значение. 0 - ложь (false), 1 - истина (true).
DECIMAL (M,D) или DEC (M,D) или NUMERIC (M,D)	M + 2 байта	зависят от параметров M и D	Используются для величин повышенной точности, например, для денежных данных. M - количество отводимых под число символов (максимальное значение - 64). D - количество знаков после запятой (максимальное значение - 30). Пример: DECIMAL (5,2) - будет хранить числа от -99,99 до 99,99.
FLOAT (M,D)	4 байта	мин. значение +(-) 1.175494351 * 10^-39 макс. значение +(-) 3. 402823466 * 10³⁸	Вещественное число (с плавающей точкой). Может иметь параметр UNSIGNED, запрещающий отрицательные числа, но диапазон значений от этого не изменится. M - количество отводимых под число символов. D - количество символов дробной части. Пример: FLOAT (5,2) - будет хранить числа из 5 символов, 2 из которых будут идти после запятой (например: 46,58).
DOUBLE (M,D)	8 байт	мин. значение +(-) 2.2250738585072015 * 10^-308 макс. значение +(-) 1.797693134862315 * 10³⁰⁸	Аналогично предыдущему, но с большим диапазоном. Пример: DOUBLE - будет хранить большие дробные числа.

Необходимо понимать, чем больше диапазон значений у типа данных, тем больше памяти он занимает. Поэтому, если предполагается, что значения в столбце не будут превышать 100, то используйте тип TINYINT. Если при этом все значения будут положительными, то используйте атрибут UNSIGNED. Правильный выбор типа данных позволяет сэкономить место для хранения этих данных.

Строковые типы данных

Тип данных	Объем памяти	Максимальный размер	Описание
CHAR (M)	M символов	М символов	Позволяет хранить строку фиксированной длины М. Значение М - от 0 до 65535. Примеры: CHAR (8) - хранит строки из 8 символов и занимает 8 байтов. Например, любое из следующих значений: '', 'Иван','Ирина', 'Сергей' будет занимать по 8 байтов памяти. А при попытке ввести значение 'Александра', оно будет усечено до 'Александ', т.е. до 8 символов.
VARCHAR (M)	L+1 символов	М символов	Позволяет хранить переменные строки длиной L. Значение М - от 0 до 65535. Примеры: VARCHAR (3) - хранит строки максимум из 3 символов, но пустая строка '' занимает 1 байт памяти, строка 'a' - 2 байта, строк 'aa' - 3 байта, строка 'aaa' - 4 байта. Значение более 3 символов будет усечено до 3.
BLOB, TEXT	L+2 символов	2¹⁶-1 символов	Позволяют хранить большие объемы текста. Причем тип TEXT используется для хранения именно текста, а BLOB - для хранения изображений, звука, электронных документов и т.д.
MEDIUMBLOB, MEDIUMTEXT	L+3 символов	2²⁴-1 символов	Аналогично предыдущему, но с большим размером.
LONGBLOB, LONGTEXT	L+4 символов	2³²-1 символов	Аналогично предыдущему, но с большим размером.
ENUM ('value1', 'value2', ...,'valueN')	1 или 2 байта	65535 элементов	Строки этого типа могут принимать только одно из значений указанного множества. Пример: ENUM ('да', 'нет') - в столбце с таким типом может храниться только одно из имеющихся значений. Удобно использовать, если предусмотрено, что в столбце должен храниться ответ на вопрос.
SET ('value1', 'value2', ...,'valueN')	до 8 байт	64 элемента	Строки этого типа могут принимать любой или все элементы из значений указанного множества. Пример: SET ('первый', 'второй') - в столбце с таким типом может храниться одно из перечисленных значений, оба сразу или значение может отсутствовать вовсе.

Календарные типы данных

Тип данных	Объем памяти	Диапазон	Описание
DATE	3 байта	от '1000-01-01' до '9999-12-31'	Предназначен для хранения даты. В качестве первого значения указывается год в формате "YYYY", через дефис - месяц в формате "ММ", а затем день в формате "DD". В качестве разделителя может выступать не только дефис, а любой символ отличный от цифры.
TIME	3 байта	от '-838:59:59' до '838:59:59'	Предназначен для хранения времени суток. Значение вводится и хранится в привычном формате - hh:mm:ss, где hh - часы, mm - минуты, ss - секунды. В качестве разделителя может выступать любой символ отличный от цифры.
DATATIME	8 байт	от '1000-01-01 00:00:00' до '9999-12-31 23:59:59'	Предназначен для хранения и даты и времени суток. Значение вводится и хранится в формате - YYYY-MM-DD hh:mm:ss. В качестве разделителей могут выступать любые символы отличные от цифры.
TIMESTAMP	4 байта	от '1970-01-01 00:00:00' до '2037-12-31 23:59:59'	Предназначен для хранения даты и времени суток в виде количества секунд, прошедших с полуночи 1 января 1970 года (начало эпохи UNIX).
YEAR (M)	1 байт	от 1970 до 2069 для М=2 и от 1901 до 2155 для М=4	Предназначен для хранения года. М - задает формат года. Например, YEAR (2) - 70, а YEAR (4) - 1970. Если параметр М не указан, то по умолчанию считается, что он равен 4.

‹ Попередня сторінка Наступна сторінка ›