Что значит unsigned в mysql
For integer data types, M indicates the maximum display width. The maximum display width is 255. Display width is unrelated to the range of values a type can store, as described in Section 11.1.6, “Numeric Type Attributes”.
For floating-point and fixed-point data types, M is the total number of digits that can be stored.
As of MySQL 8.0.17, the display width attribute is deprecated for integer data types; you should expect support for it to be removed in a future version of MySQL.
If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types; you should expect support for it to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of this attribute. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.
A bit-value type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.
Some things you should be aware of with respect to BIGINT columns:
MySQL can handle BIGINT in the following cases:
When using integers to store large unsigned values in a BIGINT column.
You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section B.3.4.7, “Solving Problems with No Matching Rows”.
A floating-point number. p represents the precision in bits, but MySQL uses this value only to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT with no M or D values. If p is from 25 to 53, the data type becomes DOUBLE with no M or D values. The range of the resulting column is the same as for the single-precision FLOAT or double-precision DOUBLE data types described earlier in this section.
FLOAT( p ) syntax is provided for ODBC compatibility.
M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits permitted by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.
Выбор типов данных в Mysql
При создании таблиц в Mysql мы определяем типы данных и дополнительные правила для колонок (размер, индексы, ограничения):
Пример создания таблицы в Mysql
Как выбрать “правильные” типы данных? Очень просто — нужно соответствовать правилу “чем меньше тем лучше”.
Чем меньше места будут занимать значения в таблице, тем проще будет базе данных читать и записывать их.
Нужны ли все колонки?
Для начала задайте своему приложению пару вопросов. Нужны ли все колонки? Возможно хватит только некоторых?
Не старайтесь угадать будущее. Практически гарантировано, что вы будете изменять структуру таблицы со временем. Остановитесь только на тех данных, которые нужны сейчас.
Какая самая короткая версия данных?
Стоит ли хранить пол (gender) пользователя в полную длину? Или достаточно будет одной буквы (f/m)? Стоит ли хранить телефон пользователя с кодом страны, либо достаточно будет только прямого номера?
Сократите длину всех колонок до минимума
Задайте эти вопросы всем колонкам будущей таблицы.
NULL значения
Значение NULL в Mysql — это специальное значение. Для работы с ним предусмотрены специальные функции. Для его обработки нужна дополнительная логика. Хорошим правилом будет избегать использования этого значения. Вместо этого можно использовать пустые значения для строк либо нули для чисел:
Значение NULL использоваться не будет
Однако не воспринимайте это, как ограничение. В некоторых случаях удобно использовать NULL, чтобы обозначить отсутсвие значения. Например, в DATETIME колонках:
Для всех числовых колонок обязательно рассчитайте максимальное значение. В Mysql существует 4 целочисленных типа:
Выбирайте минимальный тип данных исходя из максимального значения колонки.
Это может быть не так много для диска, зато критично для оперативной памяти.
UNSIGNED
Если отрицательное число неактуально для колонки, используйте UNSIGNED значения. Тогда максимально значение будет в два раза больше, однако минимальным будет ноль:
Длинна числовых типов
В Mysql можно указать длину колонки после указания числового типа:
Это не имеет никакого влияния ни на размер колонки ни на максимальное число. Просто никогда не используйте длину для числовых типов.
Большие числа
Для хранения очень больших точных чисел Mysql предлагает использовать тип DECIMAL :
Использование DECIMAL для нецелого числа
В скобках указываются количество количество цифр всего и их количество после запятой (может быть нулем). Поскольку процессоры не поддерживают математических операций с подобными числами, Mysql все подсчеты делает на своей стороне. А значит, это очень медленно.
FLOAT / DOUBLE
Используйте FLOAT / DOUBLE вместо DECIMAL, если вам не нужны очень точные числа
VARCHAR / CHAR
При выборе типов строк также действует правило минимума. Оцените максимальную длину строки и поставьте ограничение. Тип CHAR — тип фиксированной длины. Это значит, что для любой строки будет выделено всегда одно и то же количество байт:
для колонки будет всегда выделено место под 2 символа (даже, если ее значение будет пустым)
VARCHAR — тип переменной длины. В такой колонке строка будет занимать ровно свою длину (в количестве символов):
колонка будет содержать от 1 до 32 символов в зависимости от значения
Однако Mysql прибавит еще 1 или 2 байта на хранение длины самой строки. Также стоит учесть, что обновление такой строки может быть дорогой операцией (чревато фрагментацией данных, а значит — замедлением чтения). Используйте такое правило:
Если значения в текстовой колонке похожи по длине, выбирайте CHAR, иначе — VARCHAR.
BLOB / TEXT
Типы TEXT и BLOB отличаются между собой только тем, что для второго типа Mysql не делает преобразования кодировок (хранит, как есть).
Не используйте TEXT/BLOB типы для сортировочных колонок
Mysql не умеет выполнять сортировку по этим значениям, поэтому использует только первые max_sort_length символов. Точно также, при создании индекса по этой колонке необходимо указать длину:
CREATE INDEX article_body ON articles ( body(32) );
Указываем длину колонки для индексации
Тяжело представить зачем нужно индексировать текстовые колонки, просто не делайте этого. Если же захотите использовать возможность создания индекса для проверки уникальности, используйте вспомогательную колонку для хранения md5 хэша от текста:
По колонке body_md5 можно создать уникальный индекс
Преимущество этого типа в том, что он записывает номер значения вместо самого значения в каждую строку. Этим обеспечивается огромная экономия места.
Не используйте этот тип для динамических значений. Если есть список значений, который может расширяться (например, категории товаров), используйте отдельную таблицу с идентификатором:
Не используйте ENUM для динамических значений
DATETIME / TIMESTAMP
Оба формата дат позволяют хранить значения даты и времени вплоть до секунд. Однако между ними есть отличия:
Используйте формат TIMESTAMP для простановки дат событий (для чего он и создан). Например, время регистрации пользователя или публикации комментария. К тому же, он имеет удобный механизм инициализации и обновления:
Инициализация и автообновление колонок TIMESTAMP
TL;DR версия
Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Как исправить ошибку доступа к базе 1045 Access denied for user
Примеры ad-hoc запросов и технологии для их исполнения
Основные понятия о шардинге и репликации
Настройка Master-Master репликации на MySQL за 6 шагов
Как создать и использовать составной индекс в Mysql
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Check-unused-keys для определения неиспользуемых индексов в базе данных
Синтаксис и оптимизация Mysql LIMIT
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Типы и способы применения репликации на примере MySQL
Запрос для определения версии Mysql: SELECT version()
Правильная настройка Mysql под нагрузки и не только. Обновлено.
3 примера установки индексов в JOIN запросах
Быстрый подсчет уникальных значений за разные периоды времени
И как правильно работать с длительными соединениями в MySQL
Анализ медленных запросов с помощью EXPLAIN
Описание, рекомендации и значение параметра query_cache_size
Что значит и как это починить
Использование партиций для ускорения сложных удалений
Просмотр профиля запросов в Mysql
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL
Уменьшение времени выключения и включения Mysql в 3. 5 раз.
Типы данных MySQL. Чем руководствоваться при выборе типа данных
Опубликовано: 23 Апреля 2019
Выбирая тип данных для столбца, прежде всего стоит определить общий класс данных, который лучше использовать для столбца: числовые, строковые или временные;
Далее необходимо выбрать конкретный тип данных из представленных в классе. Многие типы данных MySQL позволяют хранить данные одного и тот же вида, но с разным диапазоном значений, точностью или требуемым физическим пространством (на диске или в памяти). Некоторые типы обладают специальным поведением или свойствами.
Например, в столбцах DATETIME и TIMESTAMP можно хранить один и тот же тип данных: дату и время, с точностью до секунды. Однако тип TIMESTAMP требует вдвое меньше места, позволяет работать с часовыми поясами и обладает специальными средствами автоматического обновления. С другой стороны, диапазон допустимых значений для него намного уже.
На что обратить внимание при выборе типа данных:
Скорость при соединении столбцов типа VARCHAR и ENUM:
Соединение VARCHAR с VARCHAR
Соединение VARCHAR с ENUM
Соединение ENUM с VARCHAR
Соединение ENUM с ENUM
В целях совместимости MySQL поддерживает различные псевдонимы, например INTEGER, BOOL — это псевдонимы (синонимы) одного и того же типа данных. Данный факт может сбить с толку, но не оказывает влияния на производительность.
Числовые типы
Хранение целых чисел
СУБД MySQL позволяет указывать для целых чисел «размер», например INT(11). Для большинства приложений это не имеет значения: диапазон возможных значений этим не ограничивается. Однако данный параметр говорит некоторым интерактивным инструментам MySQL, сколько позиций необходимо зарезервировать для вывода числа. С точки зрения хранения и вычисления INT(1) и INT(20) идентичны.
Знаковые и беззнаковые типы требуют одинакового пространства и обладают одинаковой производительностью.
Необязательный атрибут ZEROFILL заполнит нулями свободные позиции слева. Например с TINYINT(3) ZEROFILL, величина 2 будет записана, как 002.
| Тип данных | Бит | По умолчанию | UNSIGNED |
|---|---|---|---|
| TINYINT | 8 | -128 — 127 | 0 — 255 |
| SMALLINT | 16 | -32768 — 32767 | 0 — 65535 |
| MEDIUMINT | 24 | -8388608 — 8388607 | 0 — 16777215 |
| INT | 32 | -2147483648 — 2147483647 | 0 — 4294967295 |
| BIGINT | 64 | -9223372036854775808 — 9223372036854775807 | 0 — 18446744073709551615 |
Хранение дробных чисел
Все типы допускают приближенные математические вычисления с плавающей точкой, но в случае с FLOAT и DOUBLE операции выполняются быстрее, так как процессор выполняет их естественным для него образом.
M — количество отводимых под число символов. D — количество символов дробной части.
Для типов с плавающей точкой можно указать максимально разрешенное количество цифр до и после десятичной запятой. В случае с DECIMAL это влияет на объем пространства, требуемого для хранения данных столбца. При указании точности, в некоторых случаях, MySQL незаметно для пользователя может выбирать другой тип данных или будет округлять значения при сохранении. Поэтому рекомендуют указывать желаемый тип, но не точность.
FLOAT и DOUBLE могут иметь параметр UNSIGNED, запрещающий отрицательные числа, но диапазон значений от этого не изменится.
Число типа DECIMAL в MySQL 5.0 и более новых версиях может содержать до 65 цифр. В более ранних версиях MySQL тип DECIMAL имел предел 254 цифры и хранил значения в виде неупакованных строк (один байт на цифру). Однако эти версии СУБД не умели использовать такие большие числа в вычислениях, поскольку тип DECIMAL был просто форматом хранения. При выполнении каких-либо операций значения DECIMAL преобразовывались в тип DOUBLE.
Строковые типы
В типах CHAR и VARCHAR строки рассматриваются как последовательности символов, поэтому, при использовании многобайтных кодировок, например UNICODE, размер строки в байтах будет больше, чем в символах.
При создании таблицы нельзя комбинировать столбцы типов CHAR и VARCHAR. Если такое произойдет, то MySQL изменит тип столбцов CHAR на тип VARCHAR.
Представим что строковый тип применяется для хранения значений Y и N. В случае использования CHAR(1) значение займет один байт, тогда как для типа VARCHAR(1) потребуется два байта из-за наличия дополнительного байта длины строки.
| Значение | CHAR(4) | Требуется хранилище | VARCHAR(4) | Требуется хранилище |
|---|---|---|---|---|
| » | ‘ ‘ | 4 байта | » | 1 байт |
| ‘ab’ | ‘ab ‘ | 4 байта | ‘ab’ | 3 байта |
| ‘abcd’ | ‘abcd’ | 4 байта | ‘abcd’ | 5 байт |
| ‘abcdefgh’ | ‘abcd’ | 4 байта | ‘abcd’ | 5 байт |
Двоичные строки
Для совместимости со старыми версиями MySQL введены два специальных типа данных: BINARY и VARBINARY, которые эквивалентны типам CHAR и VARHAR, однако строка в них рассматривается как последовательность байтов, а не символов. К BINARY строкам не применимы кодировки и сортируются они как обычные последовательности байтов. Эти типы могут быть полезны, когда нужно сохранять двоичные данные, и вы хотите, чтобы MySQL сравнивал значение как байты, а не как символы. При этом, двоичное сравнение может оказаться значительно проще и быстрее символьного.
Текстовые и бинарные типы
Предназначены для хранения больших объемов двоичных или символьных данных.
MySQL обрабатывает значения BLOB и TEXT как отдельные объекты. Единственное различие между семействами BLOB и TEXT заключается в том, что типы BLOB хранят двоичные данные без учета схемы упорядочения и кодировки, а с типами TEXT ассоциированы схемы упорядочения и кодировка.
Семейство TEXT используется для хранения непосредственно текста:
Cемейство BLOB — для хранения изображений, звука, электронных документов и т.д.:
MySQL не может индексировать данные этих типов по полной длине и не может использовать для сортировки индексы.
Подсистема хранения Memory не поддерживает типы BLOB и TEXT.
Типы данных MySQL
Числовые типы данных MySQL имеют следующие дополнительные атрибуты.
UNSIGNED
Предполагается, что данное числовое значение будет неотрицательным (то есть положительным или равным нулю). То место в памяти, которое обеспечивает возможность хранить положительные или отрицательные значения в фиксированных типах данных, таких, как DECIMAL и NUMERIC, используется для хранения части числа, и это несколько увеличивает диапазон по сравнению с DECIMAL и NUMERIC. (Дополнительного атрибута SIGNED в данном случае нет.)
ZER0F1LL
Этот атрибут, используемый для отображения форматирования, заставляет MySQL дополнять числовые значения до полного размера нулями, а не пробелами. При использовании атрибута ZEROFILL автоматически включается атрибут UNSIGNED. В MySQL также принудительно вводится ограничение на отображаемый размер столбцов, составляющее 255 символов. Столбцы с данными, превышающими по длине 255 символов, хранятся должным образом, но отображаются из них только 255 символов. Числовые типы с плавающей точкой могут иметь после десятичной точки не более 30 цифр.
В приводимом ниже перечне видно, что MySQL поддерживает большую часть типов SQL 2003, а также в нем есть несколько дополнительных типов для хранения списков значений, а также больших двоичных объектов (BLOB). К расширениям стандарта ANSI относятся типы TEXT, ENUM, SET и MEDIUMINT. К специальным атрибутам, выходящим за рамки стандарта ANSI, также относятся AUTOINCREMENT, BINARY, NULL, UNSIGNED и ZEROFILL.
BIGINT[(n)] [UNSIGNED] [ZEROFILL] (тип данных SQL 2003: BIGINT)
BIT, BOOL (тип данных SQL 2003: отсутствует)
BLOB (тип данных SQL 2003: BLOB)
Хранит до 65 535 байт данных. Поддержка индексирования столбцов BLOB есть только в версиях MySQL 3.23.2 и выше (этой возможности нет больше ни в одной из рассматриваемых в книге платформ). В MySQL тип BLOB функционально эквивалентен типу MySQL VARCHAR BINARY (который обсуждается ниже) с заданным по умолчанию верхним ограничением на размер. При сравнении данных типа BLOB нужно всегда учитывать регистр. Отличие этого типа от используемого в MySQL типа VARCHAR BINARY состоит в том, что нельзя устанавливать значения по умолчанию (DEFAULT), а также в том, что в BLOB не удаляются заключительные пробелы. К столбцам BLOB не следует применять операции GROUP BY и ORDER BY. Они также хранятся отдельно от таблицы, в то время как все прочие типы данных MySQL сохраняются в структуре самого файла таблицы.
CHAR(n) [BINARY], CHARACTER [BINARY] (тип данных SQL 2003: CHARACTER))
Содержит символьную строку фиксированной длины от 1 до 255 символов. При сохранении значений тип CHAR дополняется пробелами, а при извлечении пробелы обрезаются, как в типе ANSI SQL 2003 VARCHAR. Атрибут BINARY позволяет производить поиск двоичных данных, а не только словарный поиск без учета регистра.
DATE (тип данных SQL 2003: DATE)
Хранит дату в диапазоне от 1000-01-01 до 9999-12-31 (ограниченную кавычками). По умолчанию MySQL отображает эти значения в формате ГГГГ-ММ-ДД, хотя пользователь может указать и какой-нибудь другой формат отображения.
DAТЕ TIME (тип данных SQL 2003: TIMESTAMP)
Хранит значение даты и времени в диапазоне от 1000-01-01 00:00:00 до 9999-12-31 23:59:59.
DECIMAL [р, (я)] [ZEROFILL] (тип данных SQL 2003: DECIMAL(PRECISION, SCALE))
INT [EGER] [UNSIGNED] [ZEROFILL] [AUTO [INCREMENT] (тип данных SQL 2003: INT, INTEGER)
LONGBLOB (тип данных SQL 2003: BINARY LARGE OBJECT)
Хранит данные BLOB длиной до 4 294 967 295 символов. Заметьте, что для некоторых протоколов связи клиент/сервер такой объем данных может оказаться слишком большим.
LONGTEXT (тип данных SQL 2003: CLOB)
Хранит данные типа TEXT длиной до 4 294 967 295 символов. Заметьте, что для некоторых протоколов связи клиент/сервер такой объем данных может оказаться слишком большим.
MED1UMBLOB (тип данных SQL 2003: отсутствует)
Хранит данные типа BLOB длиной до 16 777 215 символов.
MEDIUMTEXT (тип данных SQL 2003: отсутствует)
Хранит данные типа TEXT длиной до 16 777 215 символов.
MEDIUMINTf(n)] [UNSIGNED] [ZEROFILL] (тип данных SQL 2003: отсутствует)
NCHAR(n) [BINARY], [NATIONAL] CHAR(n) [BINARY] (тип данных SQL 2003: NCHAR(n))
Синонимы для CHAR. Типы NCHAR обеспечивают поддержку набора UNICODE, начиная с MySQL 4.1.
NUMERIC(p, s) (тип данных SQL 2003: DECIMAL) Синоним типа DECIMAL.
NVARCHAR(n) [BINARY], [NATIONAL] VARCHAR(n) [BINARY], NATIONAL CHAR
VARYING(n) [BINARY] (тип данных SQL 2003: NCHAR VARYING)
Синонимы типа VARYING [BINARY]. Хранит символьные строки переменной длины до 255 символов. Если не используется ключевое слово BINARY, значения хранятся и сравниваются без учета регистра.
REAL (тип данных SQL 2003: REAL) Синоним типа DOUBLE PRECISION.
SЕТ(«знач1», «знач2», …п) (тип данных SQL 2003: отсутствует)
Символьный тип данных, значения которого должны быть равны нулю или нескольким значениям из указанного списка. Список может содержать до 64 элементов.
SMALLINT[(n)] [UNSIGNED] [ZEROFILL] (тип данных SQL 2003: SMALLINT)
TEXT (тип данных SQL 2003: отсутствует)
Хранит до 65 535 символов данных. Данные типа TEXT хранятся отдельно от таблиц, в то время как остальные типы хранятся в структуре файла соответствующей таблицы. Тип TEXT функционально эквивалентен типу VARCHAR без указания верхней границы (за исключением максимального размера столбца). Сравнение выполняется без учета регистра. Тип TEXT отличается от стандартного типа VARCHAR тем, что нельзя указывать значения по умолчанию (DEFAULT), а также тем, что в ТЕХТ удаляются заключительные пробелы. К столбцам TEXT не следует применять операции GROUP BY и ORDER BY. Кроме того, поддержка индексирования столбцов TEXT появилась только в MySQL 3.23.2 и выше.
Дополнительная информация по теме
Какие основные типы данных используются в платформе PostgreSQL, отличие от стандарта SQL
Какие основные типы данных используются в платформе SQL Server, отличие от стандарта SQL
Основные типы данных используемые в платформе DB2, отличие от классического стандарта
Какие типы данных используются в платформе Oracle, основные отличия от стандарта SQL
Числовые типы данных
Необходимо понимать, чем больше диапазон значений у типа данных, тем больше памяти он занимает. Поэтому, если предполагается, что значения в столбце не будут превышать 100, то используйте тип TINYINT. Если при этом все значения будут положительными, то используйте атрибут UNSIGNED. Правильный выбор типа данных позволяет сэкономить место для хранения этих данных.
Строковые типы данных
Календарные типы данных
Тип данных NULL
Вообще-то это лишь условно можно назвать типом данных. По сути это скорее указатель возможности отсутствия значения. Например, когда вы регистрируетесь на каком-либо сайте, вам предлагается заполнить форму, в которой присутствуют, как обязательные, так и необязательные поля. Понятно, что регистрация пользователя невозможна без указания логина и пароля, а вот дату рождения и пол пользователь может указать по желанию. Для того, чтобы хранить такую информацию в БД и используют два значения:
NOT NULL (значение не может отсутствовать) для полей логин и пароль,
NULL (значение может отсутствовать) для полей дата рождения и пол.
По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать.
Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата) необязательное.
Все, на этом урок, посвященный типам данных, закончен. У вас, возможно, остались вопросы, но они исчезнут по мере освоения дальнейшего материала, т.к. на практике все становится более понятно, чем в теории.
Видеоуроки php + mysql
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
