SQL — Урок 2. Типы данных
Этот урок носит больше теоретический характер, но пропустить его нельзя. В дальнейшем вы сможете возвращаться к нему, как к справочному уроку, сейчас же просто ознакомьтесь. В прошлом уроке говорилось, что MySQL поддерживает числовые, строковые, календарные данные и данные типа NULL. Рассмотрим их по очереди.
Числовые типы данных
| Тип данных | Объем памяти | Диапазон | Описание |
| TINYINT (M) | 1 байт | от -128 до 127 или от 0 до 255 | Целое число. Может быть объявлено положительным с помощью ключевого слова UNSIGNED, тогда элементам столбца нельзя будет присвоить отрицательное значение. Необязательный параметр М — количество отводимых под число символов. Необязательный атрибут ZEROFILL позволяет свободные позиции по умолчанию заполнить нулями. |
TINYINT — хранит любое число в диапазоне от -128 до 127.
TINYINT UNSIGNED — хранит любое число в диапазоне от 0 до 255.
TINYINT (2) — предполагается, что значения будут двузначными, но по факту будет хранить и трехзначные.
SMALLINT — хранит любое число в диапазоне от -32768 до 32767.
SMALLINT UNSIGNED — хранит любое число в диапазоне от 0 до 65535.
SMALLINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и пятизначные.
MEDIUMINT — хранит любое число в диапазоне от -8388608 до 8388608.
MEDIUMINT UNSIGNED — хранит любое число в диапазоне от 0 до 16777215.
MEDIUMINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить и семизначные.
INT — хранит любое число в диапазоне от -2147683648 до 2147683648.
INT UNSIGNED — хранит любое число в диапазоне от 0 до 4294967295.
INT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.
BIGINT — хранит любое число в диапазоне от -2 63 до 2 63 -1.
BIGINT UNSIGNED — хранит любое число в диапазоне от 0 до 2 64 .
BIGINT (4) — предполагается, что значения будут четырехзначные, но по факту будет хранить максимально возможные.
Необходимо понимать, чем больше диапазон значений у типа данных, тем больше памяти он занимает. Поэтому, если предполагается, что значения в столбце не будут превышать 100, то используйте тип TINYINT. Если при этом все значения будут положительными, то используйте атрибут UNSIGNED. Правильный выбор типа данных позволяет сэкономить место для хранения этих данных.
Строковые типы данных
| Тип данных | Объем памяти | Максимальный размер | Описание |
| CHAR (M) | M символов | М символов | Позволяет хранить строку фиксированной длины М. Значение М — от 0 до 65535. |
Календарные типы данных
| Тип данных | Объем памяти | Диапазон | Описание |
| 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. |
Тип данных NULL
Вообще-то это лишь условно можно назвать типом данных. По сути это скорее указатель возможности отсутствия значения. Например, когда вы регистрируетесь на каком-либо сайте, вам предлагается заполнить форму, в которой присутствуют, как обязательные, так и необязательные поля. Понятно, что регистрация пользователя невозможна без указания логина и пароля, а вот дату рождения и пол пользователь может указать по желанию. Для того, чтобы хранить такую информацию в БД и используют два значения:
NOT NULL (значение не может отсутствовать) для полей логин и пароль,
NULL (значение может отсутствовать) для полей дата рождения и пол.
По умолчанию всем столбцам присваивается тип NOT NULL, поэтому его можно явно не указывать.
create table users (login varchar(20), password varchar(15), sex enum(‘man’, ‘woman’) NULL, date_birth date NULL);
Таким образом, мы создаем таблицу с 4 столбцами: логин (не более 20 символов) обязательное, пароль (не более 15 символов) обязательное, пол (мужской или женский) не обязательное, дата рождения (тип дата) необязательное.
Все, на этом урок, посвященный типам данных, закончен. У вас, возможно, остались вопросы, но они исчезнут по мере освоения дальнейшего материала, т.к. на практике все становится более понятно, чем в теории.
Онлайн-курс. Освойте востребованную профессию с зарплатой от 70 000 руб в месяц!
Теперь нажмите кнопку, что бы не забыть адрес и вернуться к нам снова.
Типы данных
Рассмотрим способы задания целочисленных типов данных:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INT[(M)] [UNSIGNED] [ZEROFILL]
BIGINT[(M)J [UNSIGNED] [ZEROFILL]
Также есть синонимы типов:
BOOL, BOOLEAN синонимы для TINYINT(l)
INTEGER[(M)] [UNSIGNED] [ZEROFILL] синоним для INT
ВНИМАНИЕ! Здесь и далее фразы, указанные в квадратных скобках, можно не задавать, т.е. это не обязательные параметры.
Рассмотрим не обязательные параметры для целочисленных типов:
UNSIGNED — атрибут указывает на то, что для хранения данных недопустимо использовать отрицательные значения.
ZEROFILL — атрибут указывает на то, что в случае необходимости число должно быть дополнено ведущими нулями до нужной размерности. Например, для столбца, объявленного как INT(4) ZEROFILL, величина 3 извлекается как 0003. Если для столбца указать параметр ZEROFILL, то MySQL будет автоматически добавлять в этот столбец атрибут UNSIGNED.
М — атрибут указывает максимальный размер вывода (количество выводимых символов). Максимально допустимый размер вывода составляет 255 символов.
Не стоит путать максимальный размер вывода с возможным диапазоном значений. Например, если для столбца объявить тип INT(5) UNSIGNED, то это не значит, что максимально в этот столбец можно поместить число 99999. Данный параметр лишь позволяет некоторым инструментам MySQL выделить необходимое количество позиций под вывод числа. С точки зрения хранения и вычисления INT(l), INT(5) и INT(25) идентичны.
Рассмотрим этот случай. В базе данных создана таблица с тремя столбцами id, nl и п2, для которых определены типы данных INT(l), INT(5) и INT(25) соответственно. При заполнении этой таблицы через интерфейс phpMyAdmin система выделит области ввода разной длины для этих столбцов (рис. 1)
Поле Тип Функция
Рисунок 1 — Заполнение таблицы с помощью интерфейса phpMyAdmin
Если просмотреть введенные данные, то мы увидим следующий
- 112
- ? Изменить >с Копировать ф Удалить 1 2345 23
Изменить >с Копировать Удалить 2 23 2345678
Если мы введем другие данные: 3, 2147483647 и 1, то результат будет следующий:
О Изменить >c Копировать @ Удалить
? Изменить >c Копировать @ Удалить
? Изменить >c Копировать Q Удалить
Обратите внимание, что для столбца n 1 задано ограничение на 5 символов вывода, но хранить в этом столбце вы можете число гораздо большей разрядности, в нашем случае 10-значное число 2147483647. Оно не обрезалось и не вызвало ошибки при своем вводе.
Аналогичный результат вы получите, если будите работать непосредственно с MySQL через командную строку (рис. 2 и 3).
Рисунок 2 — Результат после добавления первой строки
Рисунок 3 — Результат после добавления второй строки
Обратим внимание, что знаковые и беззнаковые (UNSIGNED) варианты типов требуют одинакового пространства — N бит (см. 2 столбец в табл. 1). В случае знакового варианта тип может хранить значения от -2 (N4) до 2 |N-I) — 1. В случае беззнакового числа диапазон значений измеряется от 0 до 2 N . Например, тип TINYINT, который занимает 1 байт (8 бит) позволяет хранить числа от-128 до 127, а тип TINYINT UNSIGNED от 0 до 255.
Вещественные числовые типы можно разделить на точные и приближенные. Точные типы данных используются, когда важно сохранить точность числа, например, при работе с денежными данными. К точным вещественным числовым типам относятся:
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
Синонимы этого типа:
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
Здесь M — определяет общее количество цифр (точность) и D — количество знаков после десятичной точки (масштаб). Сама десятичной точки и знак «-» (для отрицательных чисел) не учитываются в М.
Если D = 0, значения не имеют десятичную точку или дробную часть. Максимальное количество цифр (М) равно 65. Максимальное количество поддерживаемых знаков после запятой (D) 30. Если D не задано, то он равен 0. Если не задано М, то оно принимает значение 10. Например, если определен тип DECIMAL(4,2), то числа в этом столбце могут быть в диапазоне от -99,99 до 99,99. А если задан тип DECIMAL(3), то диапазон возможных значений от -999 до 999.
Тип DECIMAL также может использоваться для хранения больших чисел, не помещающихся в BIGINT. Так в BIGINT максимально может хранится 20-значное число, а в DECIMAL — 65-значное.
Количество байт, выделенное для хранения данного типа, варьируется. Каждые девять десятичных цифр числа упаковывается в 4 байта. Если количество цифр в числе не кратно 9, то оставшиеся цифры занимают определенную часть от 4 байт в соответствии с таблицей 2. При этом целая и дробная часть рассчитывается по отдельности.
Таблица 2 — Хранение оставшихся цифр
Количество оставшихся цифр
Количество байт для хранения
Рассмотрим пример, число 123456789123,45 (тип определен как DECIMAL(14,2)) займет в памяти 7 байт. Расчет следующий:
4 байта за первые 9 цифр целой части + 2 байта за 3 оставшиеся цифры целой части + 1 байт за 2 цифры дробной части = 7 байт
Приближенные вещественные числовые типы:
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
FLOAT(p) [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Также есть синоним для типа DOUBLE:
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL],
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
Тип REAL может быть синонимом FLOAT, если включен режим REAL_AS_FLOAT.
Тип FLOAT является типом двоичной точности и требует 4 байт для хранения. Данный тип может хранить диапазон значений от -3.402823466Е+ 38 до -1.175494351Е-38, 0, и от 1.175494351Е-38 до 3.402823466Е+38. Таковы теоретические пределы, основанные на стандарте IEEE. Фактический диапазон может быть немного меньше, в зависимости от оборудования и операционной системы.
Тип FLOAT имеет две вариации.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
параметр М задает общее количество цифр, а параметр D — количество цифр после десятичной точки. Если М и D опущены, значения сохраняются в пределах, допускаемых оборудования. Для хранения числа используется 4 байта.
FLOAT(p) [UNSIGNED] [ZEROFILL]
используется дополнительный параметр р, который равен количеству битов для хранения значения дробной части и определяет точность вещественного числа. Если р имеет значение 2 ), a FLOAT(4) — не более 16 знаков после запятой (2 4 ). Обратите внимание, что числа при сохранении округляются. В случае задания FLOAT с двумя параметрами число выводится с округлением по правилам, указанным ранее. Для столбца f3 для вывода всего числа выделяется четыре позиции (знаки «.» и «-» при этом не учитываются), при этом две позиции из четырех выделяются под вещественную часть. Первое значение столбца f4 равно 9.999, так как исходное число превышает максимально допустимое для данного типа, как раз 9.999.
Тип DOUBLE задается аналогично типу FLOAT(M,D). Основное отличие состоит в том, что для его хранения нужно 8 байт и, соответственно, он хранит число удвоенной точности.
Обратим внимание, что использование приближенных числовых типов может привести к ряду проблем:
- 1. Результаты хранения получаются округленными, т.е. заведомо не точными. У типа DOUBLE неточности проявляются при более большем количестве знаков в числе, чем у типа FLOAT.
- 2. Проблемы с поиском по конкретным значениям полей.
Поэтому в случае, если необходимо хранить важные числовые значения вещественного типа, лучше использовать тип DECIMAL.
Zerofill mysql что это
Прежде чем продолжать разрабатывать наше творение я приведу краткую справку по MySQL, а точнее, коротко опишу типы данных mysql, функции и особенности sql-запросов. Сам sql, я думаю, Вы знаете (если это не так — то к вашим услугам соответствующий раздел про SQL на «Первых шагах»! 😉
Я не буду описывать подробно все изобилие типов MySQL. Я уверен, что это лишнее. Дело в том, что в mysql-е числовые и текстовые группы типов очень многочисленны. Отличаются типы в этих группах размером в байтах и возможными модификаторами. Подробности можно посмотреть в мануале в директории Docs каталога с mysql-ем в разделе «7.3 Column types«. Там и английский знать особо не нужно — смотрите на название и цифры длины! 😉
Здесь я сделаю обзор самих групп типов данных — для быстрого введение.
- Числовые. Возможные модификаторы: UNSIGNED для объявления беззнаковости, ZEROFILL для заполнения лидирующих пробелов нулями (имеется в виду внешний вид при выводе). Могут быть созданы как целые (TINYINT, SMALLINT, INT, BIGINT и пр.), так и числа с плавающей точкой (FLOAT, DOUBLE, REAL и пр.).
- Строковые. Возможные модификаторы: BINARY для объявления поля как бинарного (любые коды хранимых символов), NATIONAL — модификатор по умолчанию, — использование набора символов для сортировки, сравнения и пр. Занятный модификатор. Отвечает за конструкцию SET-группы: SET CHARACTER SET character_set_name | DEFAULT, где character_set_name может принимать значение cp1251_koi8.
Однако эти установки выставлены по умолчанию. Выходит, без модификатора результат тот же, что и с ним. Я так понял, что эти фишки для будущего использования. Модификатор для типа CHAR VARYING создает строковое поле переменной длины. - BLOB-поля — поля для хранения двоичных данных.
- TIMESTAMP — поле хранит дату и время последнего изменения записи. Это значит, что, добавив в таблицу поле типа TIMESTAMP (например, воспользовавшись конструкцией ALTER TABLE table_name ADD COLUMN column_name TIMESTAMP), Вы, не производя никаких изменений поля типа TIMESTAMP, будете в нем иметь время последней операции с записью, влияющей на содержимое строки таблицы.
- DATE, TIME, DATETIME — поля хранения даты, времени, и того, и другого. Тут, я думаю все ясно.
- YEAR — поле, добавленное в версии 3.22, — для хранения года в интервале с 1901 по 2155.
- ENUM — поле, хранящее одно из значений, указанных в списке при создании (модификации структуры) таблицы, например, ALTER TABLE tab_name ADD COLUMN col_enum ENUM(‘Ага’, ‘Угу’, ‘Ну его нафиг’).
Теперь поместить в поле col_enum одно из перечисленных значений можно так:
INSERT INTO tab_name SET col_enum='Ага' или UPDATE tab_name SET col_enum=3.
ALTER TABLE tab_name ADD COLUMN col_set SET ('один','два','три','четыре')
Теперь изменим значение поля:
UPDATE tab_name SET col_set='один' WHERE поле=значение
После такого запроса col_set будет содержать значение ‘один’.
UPDATE tab_name SET col_set=15
В следующем шаге — краткий обзор функций MySQL, используемых в SELECT-запросах и в условиях WHERE. Подробностями самого SELECT-а мы займемся несколько позже, когда в целом реализуем первый проект — книжную базу.
В чём разница int(3) int(10) int(12) и так далее? mysql
В чём разница в mysql для полей объявленных как int(3) int(5) int(10) int(12) и так далее? Опытным путём определил, что для каждого из этих полей максимально возможное значение, которое можно вставить (для unsigned) является 4294967295. Если вставлять значение большее, то оно будет приведено к 4294967295.
короткая ссылка на этот вопрос: close
спросил 10 лет назад
| еятельность в области архитектуры, инженерных изысканий и предоставление технических консультаций в этих областях (71.1) – anonymous 3 года назад |
3 ответа
int(3), int(5), int(10), int(11) в скобочках означает не максимальную длину поля в mysql, как это делается для типа varchar. А означает лишь длину вывода. Причём только тогда, когда у столбца задан аттрибут ZEROFILL.
Т.е. число в скобках никак не влияет на максимально возможное значение типа int.
Рассмотрим на примере. Вот как будут выводиться числа для поля INT(2) unsigned:
//INT(2) UNSIGNED: 1 2 .. 98 99 .. 9999 10000
Вот как будут выводиться числа для поля INT(2) unsigned zerofill:
//INT(2) UNSIGNED ZEROFILL: 01 02 . 09 . 99 100 . 10000
//INT(4) UNSIGNED ZEROFILL: 0001 0002 . 0009 . 0099 0100 . 9999 10000
Т.е. суть в том, что для ZEROFILL будут отображаться нули слева, формируя заданную в скобочках длину вывода. Если длина строкового представления числа меньше размера вывода (размера, указанного в скобках), то строковое представление числа будет дополнено нулями слева до требуемой длины.
Еще пару реальных sql-примеров с int и zerofill:
CREATE TABLE test_int ( id1 INT UNSIGNED, id2 INT(2) UNSIGNED, id3 INT(10) UNSIGNED, id4 INT(2) UNSIGNED ZEROFILL, id5 INT(10) UNSIGNED ZEROFILL ); mysql> show create table test_int; CREATE TABLE `test_int` ( `id1` int(10) unsigned DEFAULT NULL, `id2` int(2) unsigned DEFAULT NULL, `id3` int(10) unsigned DEFAULT NULL, `id4` int(2) unsigned zerofill DEFAULT NULL, `id5` int(10) unsigned zerofill DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO test_int VALUES (1, 1, 1, 1, 1), (333,333,333,333,333); mysql> select * from test_int; +------+------+------+------+------------+ | id1 | id2 | id3 | id4 | id5 | +------+------+------+------+------------+ | 1 | 1 | 1 | 01 | 0000000001 | | 333 | 333 | 333 | 333 | 0000000333 | +------+------+------+------+------------+ 2 rows in set (0,01 sec)
ответил 8 лет назад
INT(n) в MySQL — Значение n в скобках влияет только на длину вывода. Причём только в случае, если задан аттрибут zerofill.
На длину или размерность поля n никак не влияет.
- при INT(4) ZEROFILL 4 извлекается как 0004.
- при INT(4) ZEROFILL 40001004 извлекается как 40001004.
- при INT(4) величина 4 извлекается как 4.
- при INT(4) величина 40001004 извлекается как 40001004.
Поля mysql, объявленные как int(1) int(3) int(10) int(12) — занимают одинаковое количество байт. 4 байта. Поэтому нет смысла играться с этой цифрой. Лучше использовать другой тип поля, например tinyint.
| Тип поля | Размерность (в байтах) | Максимальная длина | Максимальная длина для беззнакового (UNSIGNED) |
| SMALLINT | 1 байт | -128 до 127 | 0 до 255 |
| TINYINT | 2 байта | -32768 до 32767 | 0 до 65535 |
| MEDIUMINT | 3 байта | -8388608 до 8388607 | 0 до 16777215 |
| INT | 4 байта | -2147483648 до 2147483647 | 0 до 4294967295 |
| BIGINT | 8 байт | -9223372036854775808 до 9223372036854775807 | 0 до 18446744073709551615 |