Типы данных. Идентификаторы. Лекция №2. Тема 4

1.

Типы данных.
Идентификаторы
Лекция №2
Тема 4
1

2.

План
• Выбор оптимальных типов данных
• Специальные типы данных
• Имена баз данных и таблиц
2

3.

Типы данных
3

4.

Выбор оптимальных типов данных
• Меньше – обычно лучше
• Просто – значит хорошо
• Насколько это возможно, избегайте значений NULL
• Бывают и исключения!
4

5.

Выбор конкретного типа
Например, в столбцах DАТЕТIМЕ и ТIМЕSТАМР можно хранить один
и тот же тип данных: дату и время с точностью до секунды.
Однако тип TIМЕSТАМР требует вдвое меньше места, позволяет
работать с часовыми поясами и обладает специальными
средствами автоматического обновления.
При этом диапазон допустимых значений для него на много уже, а
специальные средства могут стать недостатком.
5

6.

Типы данных в MySQL
Группа типов Типы
Числа
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
FLOAT, DOUBLE
NUMERIC, DECIMAL
Текст
CHAR, VARCHAR
TINYTEXT, MEDIUMTEXT, TEXT, LONGTEXT
TINYBLOB, MEDIUMBLOB, BLOB, LONGBLOB
Дата и время DATETIME, TIMESTAMP, DATE, TIME, YEAR
Битовые
BIT
Списки
ENUM, SET
6

7.

Целые числа
• TNYINT – 8 bit
• SMALLINT – 16 bit
• MEDIUMINT – 24 bit
• INT – 32 bit
• BIGINT – 64 bit
• -//- UNSIGNED
• TINYINT UNSIGNED
7

8.

Целые числа
Тип данных
Размер,
байт
Min
Max
INT
INTEGER (синоним)
4
-2 147 483 648
2 147 483 647
INT UNSIGNED (беззнаковый)
4
0
4 294 967 295
TINYINT
1
-128
127
TINYINT UNSIGNED
1
0
256
SMALLINT
2
-32 768
32 767
SMALLINT UNSIGNED
2
0
465 535
MEDIUMINT
3
-8 388 608
8 388 607
MEDIUMINT UNSIGNED
3
0
16777215
BIGINT
8
-9 223 372 036 854 775 808
9 223 372 036 854 775 807
BIGINT UNSIGNED
8
0
18 446 744 073 709 551 615
8

9.

Вещественные числа
1. с плавающей точкой
• FLOAT
• DOUBLE
2. с фиксированной точкой
• NUMERIC
• DECIMAL
9

10.

Вещественные числа с плавающей точкой
Тип данных
FLOAT(M,D)
Размер, байт
Значение
4
Десятичное число
от -3.402823466E+38 до -1.175494351E-38,
0
и от 1.175494351E-38 до 3.402823466E+38,
где M - количество выводимых цифр в числе (<=25), D - количество цифр после точки.
М и D - не обязательны. По умолчанию M=10, D=2.
Пример: для FLOAT(5,2) промежуток допустимых значений будет от-999.99 до 999,99.
FLOAT UNSIGNED
(беззнаковый)
4
DOUBLE(M,D)
8
Синонимы:
REAL(M,D)
DOUBLE
PRESICION(M,D)
DOUBLE(M,D)
UNSIGNED
Тоже что и FLOAT только без отрицательных значений
Десятичное число от -1.7976931348623157E+308 до -2.2250738585072014E-308,
0,
и от 2.2250738585072014E-308 до 1.7976931348623157E+308,
где M - количество выводимых цифр в числе (25 <= M <= 53), D - количество цифр
после точки. М и D - не обязательны. По умолчанию M=16, D=4.
Пример: для DOUBLE(5,2) промежуток допустимых значений будет от-999.99 до 999,99.
8
Тоже что и DOUBLE, только не допускает отрицательные значения
10

11.

Вещественные числа с плавающей точкой
Если число будет выходить из заданного диапазона, то MySQL будет выполнять округление.
Числа с плавающей точкой хранятся неточно (в отличие от типов с фиксированной точкой),
вычисления и сравнения могут быть непредсказуемы. Поэтому рекомендуется сравнивать не
абсолютные значения, а принять допустимую погрешность, и сравнивать разность двух
значений с плавающей точкой и допустимую погрешность.
Напр.,
DOUBLE
a = 21.4000000000000
b = 21,4
вместо a <> b
использовать 0.0001 <= ABS(a-b)
Ошибки происходят т.к., числа с плавающей точкой в MySQL представлены в двоичном виде и
в виде дроби, в результате которой никогда не может получиться целое число в двоичной
системе счисления, напр., 0.1, 0.2, 0.3 -> 1/10, 2/10, 3/10
11

12.

Вещественные числа с фиксированной
точкой
Тип данных
DECIMAL(M,D)
Синонимы:
DEC(M,D)
Размер, байт
Значение
зависит от
количества
цифр
Тоже что и DOUBLE, только число хранится в
виде строки. По умолчанию M=10, D=0.
8
NUMERIC(M,D)
DECIMAL(M,D)
UNSIGNED
8
Тоже что и DECIMAL, только не допускает
отрицательные значения
12

13.

Строковые типы
• CHAR – строка фиксированной длины. Длина от 1 до 255. Текст
меньшей длины будет дополнен пробелами в конце. По
умолчанию текст не чувствительный к регистру букв. Чтобы текст
был чувствительным к регистру букв, то нужно добавить BINARY
name CHAR(25) BINARY
13

14.

Строковые типы
• Символьные
• VARCHAR – строка переменной длины.
• Тип CHAR предпочтительнее использовать, если хранимые значения имеют
одинаковую длину или длина отличается незначительно.
• Текстовые
• BLOB представляет собой двоичную строку переменной длины,
чувствительную к регистру букв
• TEXT – текстовая строка, нечувствительная к регистру букв.
14

15.

Символьные типы
Тип данных
CHAR
Размер
Определение
Зависит от кодировки
Символьный тип. Строка неизменяемой
длины. Длина строки (Size) от 0 до 255
Зависит от кодировки
Символьный тип. Строка переменной
длины. Длина строки (Size) от 0 до 65532 (0255 для версий ниже MySQL 5). Если
значение будет большее 65 532 (255), то
оно сконвертируется в тип TEXT
Синоним
CHARACTER
VARCHAR
Синоним
CHARACTER
VARYING
15

16.

Текстовые типы
Тип данных
Размер, байт
Определение
TEXT
Длина строки + 2 байта
Строка с максимальной длиной в 65 535 символов. Для хранения больших
текстов.
BLOB
0 - 65 535
Binary Large OBjects - для хранения больших текстов. Может хранить до
65,535 байт бинарных данных (например картинки). Также используется
наравне с TEXT для хранения текста, разница в том, что BLOB хранит данные
как есть, в виде последовательностей байт
TINYTEXT
Длина строки + 1 байт
Строка с максимальной длиной в 255 символов
MEDIUMTEXT
Длина строки + 3 байта
Строка с максимальной длиной в 16 777 215 символов
LONGTEXT
Длина строки + 4 байта
Строка с максимальной длиной в 4 294 967 295 символов
TINYBLOB
0 - 255
Binary Large OBjects. Может хранить до 255 байт бинарных данных (например
картинки). Также используется наравне с TINYTEXT для хранения текста.
MEDIUMBLOB
0 - 16 777 215
Binary Large OBjects. Может хранить до 16 777 215 байт бинарных данных
(например картинки). Также используется наравне с MEDIUMTEXT для
хранения текста.
LONGBLOB
0 - 4 294 967 295
Binary Large OBjects. Может хранить до 4 294 967 295 байт бинарных данных
(например картинки). Также используется наравне с LONGTEXT для хранения
текста.
16

17.

Типы даты и времени
• YEAR – это однобайтный тип данных для хранения значения года.
• TIME – тип данных для хранения времени. Значение всегда
выводится в формате hh:mm:ss.
• DATE – тип данных для хранения года, месяца и дня.
• DATETIME – тип данных для хранения информации как о дате, так
и о времени.
• TIMESTAMP – тип данных для автоматической записи текущих
даты и времени при выполнении операций INSERT и UPDATE
17

18.

Типы даты и времени
Тип данных
Размер,
байт
Определение
Формат. Допустимое
значение
DATE
3
Дата
YYYY-MM-DD
от 1000-01-01
до 9999-12-31
DATETIME
8
Дата и время
YYYY-MM-DD HH:MM:SS
от 1000-01-01 00:00:00
до 9999-12-31 23:59:59
TIMESTAMP
4
Дата и время. Реагирует на изменения
таблицы, устанавливает дату и время
последнего изменения таблицы
YYYYMMDD HHMMSS
от 1970-01-01 00:00:01
до 2038-01-09 03:14:07
TIME
3
Время
HH:MM:SS
от 00:00:00
до 23:59:59
YEAR
1
Год
YYYY или YY
от 1901 до 2155
или от 70 до 69 (1970 - 2069)
18

19.

Битовые типы
Тип данных
BIT(M)
Размер, бит
Значение
зависит от количества цифр,
от 1 до 64
Число хранится в виде
последовательности бит,
где M - количество выводимых цифр в
числе (1 <= M <= 64).
По умолчанию M=1, если столбец не
может иметь значение NULL, то M=0 .
19

20.

Логические и специальные значения
• Логического типа данных в MySQL нет, а ключевые слова TRUE,
FALSE эквивалентны 1, 0.
• Логическое значение можно получить, если выполнять операции
сравнения: <, >, <>, !=, =, <=, >=, <=> - безопасное сравнение NULL.
• Специальное значение NULL - говорит об отсутствии значения. Но
для некоторых столбцов может стоять ограничение NOT NULL.
20

21.

Отсутствующие данные (NULL)
Базы данных обычно представляет собой модель реального мира,
отдельные элементы данных в ней неминуемо будут отсутствовать.
21

22.

Операторы булевой алгебры
Логические операторы (булевой
алгебры)
Операторы MySQL
И
AND, &&
ИЛИ
OR, ||
НЕ
NOT, !
Исключающее ИЛИ (или a или b)
XOR
22

23.

Функции и операторы сравнения 1/2
Название
>
>=
<
<>, !=
<=
<=>
=
BETWEEN ... AND ...
COALESCE()
GREATEST()
IN()
INTERVAL()
Описание
больше чем
больше или равно
меньше чем
не равно
меньше чем
безопасное сравнение с NULL
равно
в диапазоне значений
возвращает первый ненулевой аргумент
возвращает наибольший аргумент
аргумент входит в набор значений
возвращает индекс аргумента, который меньше первого аргумента
23

24.

Функции и операторы сравнения 2/2
Название
IS
IS NOT
IS NOT NULL
IS NULL
ISNULL()
LEAST()
LIKE
NOT BETWEEN ... AND ...
NOT IN()
NOT LIKE
STRCMP()
Описание
сравнение значение с булевым
отрицание от сравнения значения с булевым
является ли NOT NULL
является ли NULL
является ли аргумент NULL
возвращает наименьший аргумент
простое совпадение по шаблону (паттерну)
не в диапазоне значений
аргумент не входит в набор значений
простое несовпадение по шаблону
сравнение двух строк
24

25.

ENUM и SET
• Поле типа ENUM может содержать одно значение из
предварительно заданного набора. Каждое из допустимых
значений имеет индекс
• Поле типа SET может содержать любое количество
предварительно заданных значений. В одном наборе может быть
до 64 различных элементов
25

26.

Комплексные типы
Тип данных
Размер, байт
Определение
ENUM(a,b,c,...,n)
1-255 значений: 1 байт
256-65 535 значений: 2
Список. Максимальное количество
значений в списке - 65,535. Поле
может принимать только одно
значение из списка. При неверном
значении оставляет поле пустым.
Пример поля: Gender
ENUM("male","female").
SET(a,b,c,...,n)
1-8 значений: 1 байт
9-16 значений: 2
17-24 значений:3
25-32 значений: 4
33-64 значений: 8
Список. Похож на ENUM, но поле
может принимать несколько
значений из списка. Максимальное
количество значений в списке - 64.
Пример поля: Fruits
SET("orange","apple","kiwi").
26

27.

Комплексные типы: ENUM
• ENUM - позволяет обеспечить целостность данных, но нарушает условие 1НФ.
• ENUM объект с заранее определенными текстовыми значениями (эти значения
перечисляются явно при создании таблицы)
• Нельзя добавить значения в столбец ENUM, не указанные при создании таблицы
• Особенность ENUM: MySQL автоматически преобразует из строкового типа в
числовой и наоборот при необходимости
• Значения столбца ENUM нумеруются (индексируются) и к строкам можно
обращаться при помощи этих индексов (не путайте с индексами таблицы)
• В качестве значений может принимать только литералы (строки, числа), но не
выражения
• Вставка 1 строки = 1 байт на жестком диске, 1 000 000 строк = 1 000 000 байт. Напр.,
если есть слово из 6 букв, то 1 000 000 строк CHAR займет 6 000 000 байт, а 1 000
000 строк ENUM займет 1 000 000 байт (из-за того, что MySQL просто подставляет
индекс)
• max кол-во элементов в перечислении 65 535, но на практике max < 3 000
27

28.

Комплексные типы: SET
• SET - строковый тип данных.
• Значения SET не могут содержать запятых, т.к., запятая
используется как разделитель
• К значению можно обращаться по индексу
• Порядок написания значений не имеет значение, MySQL их все
равно вставит в том порядке, в котором были написаны при
создании таблицы
28

29.

Комплексные типы: ENUM, SET
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium',
'large', 'x-large')
);
CREATE TABLE myset (col SET('a', 'b', 'c',
'd'));
29

30.

Имена баз данных и таблиц
• Длина до 64 символов
• Нельзя использовать прямой слеш, обратный слеш и точку
• В именах таблиц и полей не рекомендуется использовать
зарезервированные слова
30