Типы данных в MySQL

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

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

Типы данных в MySQL

Все типы в MySQL можно разделить 4 группы:

1. Numeric (числа) Могут быть знаковыми (SIGNED) и без знаковыми (UNSIGNED)
   • Целочисленные
     TINYINT 1 байт от -128 до 127
     SMALLINT 2 байта от -32 768 до 32 767
     MEDIUMINT 3 байта -8 388 608 до 8 388 608
     INT 4 байта от -2 147 483 648 до 2 147 483 647
     BIGINT 8 байтов от -92 233 720 368 547 758 808 до 92 233 720 368 547 758 807
   • С плавающей точкой (дробные)
     Пример 4.3 где 4 это точность, а 3 — масштаб. Масштаб не может быть больше точности(первое число обозначает сколько символов будет в числе, а второе — сколько символов после запятой)
     DECIMAL вещественный тип данных, хранится в виде строки.
     FLOAT одинарной точности –3.402823466E+38 до –1.175494351E-38
     DOUBLE двойной точности от -1.7976931348623157E+308 до -2.2250738585072014E-308
     REAL Аналог DOUBLEBIT
     BOOL
     SERIAL
2. Data and Time (дата и время)
   DATETIME пример: ‘0000-00-00 00:00:00’
   DATE пример: ‘0000-00-00’
   TIMESTAMP пример: 00000000000000 (длина зависит от количества выводимых символов)
   TIME пример: ’00:00:00′
   YEAR пример: 0000
3. Символьные (текстовые значения)
   CHAR Строка фиксированной длины (0-255, по-умолчанию 1), при хранении всегда дополняется пробелами в конце строки до заданной длины
   VARCHAR Строка переменной длины (0-65,535), эффективная максимальная длина зависит от максимального размера строки
   TINYTEXT Столбец типа TEXT с максимальной длиной 255 (2^8 — 1) символов, сохраняется с одно-байтовым префиксом, указывающим длину значения в байтах
   TEXT Столбец типа TEXT с максимальной длиной 65,535 (2^16 — 1) символов, сохраняется с двух-байтовым префиксом, указывающим длину значения в байтах
   MEDIUMTEXT Столбец типа TEXT с максимальной длиной 16,777,215 (2^24 — 1) символов, сохраняется с трех-байтовым префиксом, указывающим длину значения в байтах
   LONGTEXT Столбец типа TEXT с максимальной длиной 4,294,967,295 или 4ГиБ (2^32 — 1) символов, сохраняется с четырех-байтовым префиксом, указывающим длину значения в байтах
   BINARY Аналогичен типу CHAR, но предназначен для хранения бинарных байт-строк, вместо не бинарных символьных строк
   VARBINARY Аналогичен типу VARCHAR, но предназначен для хранения бинарных байт-строк, вместо не бинарных символьных строк
   TINYBLOB Столбец типа BLOB с максимальной длиной 255 (2^8 — 1) байт, сохраняется с одно-байтовым префиксом, указывающим длину значения
   MEDIUMBLOB Столбец типа BLOB с максимальной длиной 16,777,215 (2^24 — 1) байт, сохраняется с трех-байтовым префиксом, указывающим длину значения
   BLOB Столбец типа BLOB с максимальной длиной 65,535 (2^16 — 1) байт, сохраняется с двух-байтовым префиксом, указывающим длину значения
   LONGBLOB Столбец типа BLOB с максимальной длиной 4,294,967,295 или 4ГиБ (2^32 — 1) байт, сохраняется с четырех-байтовым префиксом, указывающим длину значения
   ENUM Перечисляемый тип данных, который может содержать максимум 65,535 различных величин или специальную величину ошибки »
   SET Единственное значение выбираемое из набора не более 64 членов
4. Пространственные
   GEOMETRY Тип для хранения любого вида геометрических данных
   POINT Точка в двухмерном пространстве
   LINESTRING Кривая с линейной интерполяцией между точек
   POLYGON Многоугольник
   MULTIPOINT Набор точек
   MULTILINESTRING Набор кривых с линейной интерполяцией между точками
   MULTIPOLYGON Набор многоугольников
   GEOMETRYCOLLECTION Набор геометрических объектов любого типа

Атрибуты в MySQL

• SIGNED число со знаком
• UNSIGNED число без знака
• UNSIGNED ZEROFILL если длина поля равна 4 символам, а в значение занесено число 2, то результат будет следующий: 0002. Причём при обращению к данной ячейке таблицы мы получим значение 2
• on update CURRENT_TIMESTAMP дата обновления записи

Сравнение в MySQL

Основной особенностью в данной характеристики является следующее: для сравнения …_bin важен регистр введённых значений, то есть строки «Текст» и «текст» являются разными! Для других характеристик сравнения данные строки будут одинаковыми.
Чаще всего …_bin используется для авторизации, при сравнении логина и пароля.

Индексы в MySQL

Индексы в MySQL – позволяют ускорить выборку данных из базы.
Мы можем создать индекс для любого поля таблицы, но рекомендуется добавлять индексы лишь тем полям, которые часто попадают в WHERE.

Пример:

1

SELECT * FROM `имя таблицы` WHERE title = 1;

В данном случае для поля title рекомендуется создать индекс.

Не стоит добавлять индексы на все поля подряд, так как индекс создаёт дополнительные (служебные) таблицы, что существенно увеличивает общий размер базы!

Виды индексов в MySQL:

• PRIMARY KEY – это первичный ключ таблицы, который может быть только один.
• Уникальный ключ (UNIQUE) – альтернатива первичному ключу.
• Составной индекс – позволяет включать в индекс несколько полей.