Урок 5. MySQL. Типы данных. Часть 2

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

В MySQL как и во многих языках программирования данные строго типизированы. Типизация данных помогает в организации и повышении оптимизации таблиц в базах данных MySQL.

В MySQL всего четыре группы типов данных:

Далее рассмотрим подробно каждый тип данных в MySQL.

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

Примечание: Значения сортируются в порядке их ввода.

Вы вводите возможные значения в этом формате: Enum (‘ X ‘, ‘ Y ‘, ‘ Z ‘) SET Аналогично Enum, за исключением того, что набор может содержать до 64 элементов списка и может хранить более одного выбора

Типы данных чисел:

* целочисленные типы имеют дополнительный параметр, называемый неподписанным. Как правило, целое число переходит от отрицательного к положительному значению. Добавление неподписанного атрибута будет перемещаться, что диапазон вверх, поэтому он начинается с нуля, а не отрицательное число.

Типы данных дат:

Примечание: Поддерживаемый диапазон от ‘ 1000-01-01 ‘ до ‘ 9999-12-31 ‘

DATETIME() * комбинация даты и времени. Формат: гггг-мм-DD HH: MI: SS

Примечание: Поддерживаемый диапазон от ‘ 1000-01-01 00:00:00 ‘ до ‘ 9999-12-31 23:59:59 ‘

TIMESTAMP() * Временная метка. Значения timestamp хранятся в виде количества секунд со времени Unix (‘ 1970-01-01 00:00:00 ‘ UTC). Формат: гггг-мм-DD HH: MI: SS

Примечание: Поддерживаемый диапазон от ‘ 1970-01-01 00:00:01 ‘ UTC до ‘ 2038-01-09 03:14:07 ‘ UTC

TIME() Время. Формат: HH: MI: SS

Примечание: Поддерживаемый диапазон от ‘-838:59:59 ‘ до ‘ 838:59:59 ‘

YEAR() Год в формате с двумя или четырьмя цифрами.

Примечание: Допустимые значения в формате четырех цифр: 1901 до 2155. Допустимые значения в формате с двумя цифрами: 70 до 69, представляющие годы с 1970 по 2069

* даже если DateTime и timestamp возвращаются в том же формате, они работают очень по-разному. В запросе INSERT или Update временная метка автоматически устанавливает текущую дату и время. TIMESTAMP также принимает различные форматы, такие как ииииммддххмисс, ииммддххмисс, ГГГГММДД или YYMMDD.

Типы данных SQL Server

Типы строковых данных:

Типы данных чисел:

Разрешает числа от-10 ^ 38 + 1 до 10 ^ 38 – 1.

Параметр p указывает максимальное общее количество цифр, которые могут быть сохранены (как слева, так и справа от десятичной запятой). p должно быть значением от 1 до 38. Значение по умолчанию — 18.

Параметр s указывает максимальное число цифр, хранящихся справа от десятичной запятой. s должно быть значением от 0 до p. значение по умолчанию 0

5-17 bytes numeric(p,s) Фиксированные значения точности и масштаба.

Разрешает числа от-10 ^ 38 + 1 до 10 ^ 38 – 1.

Параметр p указывает максимальное общее количество цифр, которые могут быть сохранены (как слева, так и справа от десятичной запятой). p должно быть значением от 1 до 38. Значение по умолчанию — 18.

Параметр s указывает максимальное число цифр, хранящихся справа от десятичной запятой. s должно быть значением от 0 до p. значение по умолчанию 0

5-17 bytes smallmoney Денежные данные от-214 748,3648 до 214 748,3647 4 bytes money Денежные данные от-922 337 203 685 477,5808 до 922 337 203 685 477,5807 8 bytes float(n) Плавающая точность данных чисел от-1.79 e + 308 до 1.79 e + 308.

Параметр n указывает, должно ли поле содержать 4 или 8 байт. float (24) содержит 4-байтное поле и float (53) содержит 8-байтное поле. Значение по умолчанию n — 53.

Урок 5. MySQL. Типы данных. Часть 2

MySQL поддерживает несколько типов столбцов, которые можно разделить на три категории: числовые типы данных, типы данных для хранения даты и времени и символьные (строковые) типы данных. В данном разделе вначале дается обзор всех возможных типов и приводятся требования по хранению для каждого типа столбца, затем свойства типов описываются более подробно по каждой категории. Мы намеренно сделали обзор кратким, поскольку более детальные описания требуют дополнительной информации о конкретных типах столбцов, например, о допустимых форматах представления величин.

Ниже перечислены типы столбцов, поддерживаемые MySQL. В описаниях используются следующие обозначения:

M Указывает максимальный размер вывода. Максимально допустимый размер вывода составляет 255 символов. D Употребляется для типов данных с плавающей точкой и указывает количество разрядов, следующих за десятичной точкой. Максимально возможная величина составляет 30 разрядов, но не может быть больше, чем M -2.

Квадратные скобки ( `[‘ и `]’ ) указывают для типа данных группы необязательных признаков.

Заметьте, что если для столбца указать параметр ZEROFILL , то MySQL будет автоматически добавлять в этот столбец атрибут UNSIGNED .

Предупреждение: следует помнить, что при выполнении вычитания между числовыми величинами, одна из которых относится к типу UNSIGNED , результат будет беззнаковым! See section 6.3.5 Функции приведения типов.

TINYINT[(M)] [UNSIGNED] [ZEROFILL] Очень малое целое число. Диапазон со знаком от -128 до 127 . Диапазон без знака от 0 до 255 . BIT BOOL Являются синонимами для TINYINT(1) . SMALLINT[(M)] [UNSIGNED] [ZEROFILL] Малое целое число. Диапазон со знаком от -32768 до 32767 . Диапазон без знака от 0 до 65535 . MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] Целое число среднего размера. Диапазон со знаком от -8388608 до 8388607 . Диапазон без знака от 0 до 16777215 . INT[(M)] [UNSIGNED] [ZEROFILL] Целое число нормального размера. Диапазон со знаком от -2147483648 до 2147483647 . Диапазон без знака от 0 до 4294967295 . INTEGER[(M)] [UNSIGNED] [ZEROFILL] Синоним для INT . BIGINT[(M)] [UNSIGNED] [ZEROFILL] Большое целое число. Диапазон со знаком от -9223372036854775808 до 9223372036854775807 . Диапазон без знака от 0 до 18446744073709551615 . Для столбцов типа BIGINT необходимо учитывать некоторые особенности:

• Все арифметические операции выполняются с использованием значений BIGINT или DOUBLE со знаком, так что не следует использовать беззнаковые целые числа больше чем 9223372036854775807 (63 бита), кроме операций, выполняемых логическими функциями. В противном случае несколько последних разрядов результата могут оказаться ошибочными из-за ошибок округления при преобразовании BIGINT в DOUBLE . MySQL 4.0 может обрабатывать данные типа BIGINT в следующих случаях:
  • Использование целых чисел для хранения больших беззнаковых величин в столбце с типом BIGINT .
  • В случаях MIN(big_int_column) и MAX(big_int_column) .
  • При использовании операторов ( `+’ , `-‘ , `*’ и т.д.), когда оба операнда являются целыми числами.
• Точное значение целого числа всегда можно хранить в столбце с типом BIGINT в виде строки. В этом случае MySQL выполнит преобразование строки в число без промежуточного преобразования.
• Если оба аргумента являются целочисленными величинами, то при выполнении над ними операций `-‘ , `+’ , и `*’ будут использоваться правила BIGINT -арифметики. Это означает, что при умножении двух больших целых чисел (или результатов вычислений функций, возвращающих целые числа) результат операции может оказаться непредсказуемым, если он превосходит значение 9223372036854775807 .

FLOAT(точность) [UNSIGNED] [ZEROFILL] Число с плавающей точкой. Атрибут точности может иметь значение для числа с плавающей точкой обычной (одинарной) точности и между 25 и 53 — для числа с плавающей точкой удвоенной точности. Эти типы данных сходны с типами FLOAT и DOUBLE , описанными ниже. FLOAT(X) относится к тому же интервалу, что и соответствующие типы FLOAT и DOUBLE , но диапазон значений и количество десятичных знаков не определены. В версии MySQL 3.23 это истинная величина числа с плавающей точкой. В более ранних версиях MySQL тип данных FLOAT(точность) всегда имеет два десятичных знака. Следует отметить, что использование типа данных FLOAT может привести к неожиданным проблемам, так как все вычисления в MySQL выполняются с удвоенной точностью. See section A.5.6 Решение проблем с отсутствием строк, удовлетворяющих условиям поиска. Данный синтаксис обеспечивает совместимость с ODBC. FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] Малое число с плавающей точкой обычной точности. Допустимые значения: от -3,402823466E+38 до -1,175494351E-38 , 0 , и от 1,175494351E-38 до 3,402823466E+38 . Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D — количество разрядов, следующих за десятичной точкой. Обозначение FLOAT без указания аргументов или запись вида FLOAT(X) , где X справедливы для числа с плавающей точкой обычной точности. DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] Число с плавающей точкой удвоенной точности нормального размера. Допустимые значения: от -1,7976931348623157E+308 до -2,2250738585072014E-308 , 0 , и от 2,2250738585072014E-308 до 1,7976931348623157E+308 . Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Атрибут M указывает количество выводимых пользователю знаков, а атрибут D — количество разрядов, следующих за десятичной точкой. Обозначение DOUBLE без указания аргументов или запись вида FLOAT(X) , где 25 справедливы для числа с плавающей точкой двойной точности. DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] REAL[(M,D)] [UNSIGNED] [ZEROFILL] Данные обозначения являются синонимами для DOUBLE . DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] «Неупакованное» число с плавающей точкой. Ведет себя подобно столбцу CHAR , содержащему цифровое значение. Термин «неупакованное» означает, что число хранится в виде строки и при этом для каждого десятичного знака используется один символ. Разделительный знак десятичных разрядов, а также знак `-‘ для отрицательных чисел не учитываются в M (но место для них зарезервировано). Если атрибут D равен 0 , величины будут представлены без десятичного знака, т.е. без дробной части. Максимальный интервал значений типа DECIMAL тот же, что и для типа DOUBLE , но действительный интервал для конкретного столбца DECIMAL может быть ограничен выбором значений атрибутов M и D . Если указан атрибут UNSIGNED , отрицательные значения недопустимы. Если атрибут D не указан, его значение по умолчанию равно 0 . Если не указан M , его значение по умолчанию равно 10 . В более ранних, чем MySQL 3.23, версиях аргумент M должен содержать в себе место для знака числа и десятичного знака. DEC[(M[,D])] [UNSIGNED] [ZEROFILL] NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] Данные обозначения являются синонимами для DECIMAL . DATE Дата. Поддерживается интервал от ‘1000-01-01’ до ‘9999-12-31’ . MySQL выводит значения DATE в формате ‘YYYY-MM-DD’ , но можно установить значения в столбец DATE , используя как строки, так и числа. See section 6.2.2.2 Типы данных DATETIME , DATE и TIMESTAMP . DATETIME Комбинация даты и времени. Поддерживается интервал от ‘1000-01-01 00:00:00’ до ‘9999-12-31 23:59:59’ . MySQL выводит значения DATETIME в формате ‘YYYY-MM-DD HH:MM:SS’ , но можно устанавливать значения в столбце DATETIME , используя как строки, так и числа. See section 6.2.2.2 Типы данных DATETIME , DATE и TIMESTAMP . TIMESTAMP[(M)] Временная метка. Интервал от ‘1970-01-01 00:00:00’ до некоторого значения времени в 2037 году. MySQL выводит значения TIMESTAMP в форматах YYYYMMDDHHMMSS , YYMMDDHHMMSS , YYYYMMDD или YYMMDD в зависимости от значений M : 14 (или отсутствующее), 12 , 8 , или 6 ; но можно также устанавливать значения в столбце TIMESTAMP , используя как строки, так и числа. Столбец TIMESTAMP полезен для записи даты и времени при выполнении операций INSERT или UPDATE , так как при этом автоматически вносятся значения даты и времени самой последней операции, если эти величины не введены программой. Можно также устанавливать текущее значение даты и времени, задавая значение NULL . See section 6.2.2 Типы данных даты и времени. Аргумент M влияет только на способ вывода столбца TIMESTAMP ; для хранения его значений всегда используется 4 байта. Следует учитывать, что столбцы TIMESTAMP(M) , где M равно 8 или 14 , представляют собой числа, в то время, как столбцы TIMESTAMP(M) с иным значением аргумента M являются строками. Это убеждает, что можно надежно сделать дамп и восстановить таблицу с этими типами столбцов! See section 6.2.2.2 Типы данных DATETIME , DATE и TIMESTAMP . TIME Время. Интервал от ‘-838:59:59’ до ‘838:59:59’ . MySQL выводит значения TIME в формате ‘HH:MM:SS’ , но можно устанавливать значения в столбце TIME , используя как строки, так и числа. See section 6.2.2.3 Тип данных TIME . YEAR[(2|4)] Год в двухзначном или четырехзначном форматах (по умолчанию формат четырехзначный). Допустимы следующие значения: с 1901 по 2155 , 0000 для четырехзначного формата года и 1970-2069 при использовании двухзначного формата ( 70-69 ). MySQL выводит значения YEAR в формате YYYY , но можно задавать значения в столбце YEAR , используя как строки, так и числа (тип данных YEAR недоступен в версиях, предшествующих MySQL 3.22). See section 6.2.2.4 Тип данных YEAR . [NATIONAL] CHAR(M) [BINARY] Строка фиксированной длины, при хранении всегда дополняется пробелами в конце строки до заданного размера. Диапазон аргумента M составляет от 0 до 255 символов (от 1 до 255 в версиях, предшествующих MySQL 3.23). Концевые пробелы удаляются при выводе значения. Если не задан атрибут чувствительности к регистру BINARY , то величины CHAR сортируются и сравниваются как независимые от регистра в соответствии с установленным по умолчанию алфавитом. Атрибут NATIONAL CHAR (или его эквивалентная краткая форма NCHAR ) представляет собой принятый в ANSI SQL способ указания, что в столбце CHAR должен использоваться установленный по умолчанию набор символов ( CHARACTER ). В MySQL это принято по умолчанию. CHAR является сокращением от CHARACTER . MySQL позволяет создавать столбец типа CHAR(0) . В основном это полезно, если необходимо обеспечить совместимость с некоторыми старыми приложениями, которые зависят от наличия столбца, но реально эту величину не используют. Кроме того, такая возможность может очень пригодиться в случае, если необходим столбец, который может содержать только 2 значения, а именно CHAR(0) (т.е. столбец, который не определен как NOT NULL , занимает только один бит и принимает только 2 значения: NULL или «» ). See section 6.2.3.1 Типы данных CHAR и VARCHAR. CHAR Это синоним для CHAR(1) . [NATIONAL] VARCHAR(M) [BINARY] Строка переменной длины. Примечание: концевые пробелы удаляются при сохранении значения (в этом заключается отличие от спецификации ANSI SQL). Диапазон аргумента M составляет от 0 до 255 символов (от 1 до 255 в версиях, предшествующих MySQL Version 4.0.2). Если не задан атрибут чувствительности к регистру BINARY , то величины VARCHAR сортируются и сравниваются как независимые от регистра. See section 6.5.3.1 Молчаливые изменения определений столбцов. Термин VARCHAR является сокращением от CHARACTER VARYING . See section 6.2.3.1 Типы данных CHAR и VARCHAR. TINYBLOB TINYTEXT Столбец типа BLOB или TEXT с максимальной длиной 255 ( 2^8 — 1 ) символов. See section 6.5.3.1 Молчаливые изменения определений столбцов. See section 6.2.3.2 Типы данных BLOB и TEXT . BLOB TEXT Столбец типа BLOB или TEXT с максимальной длиной 65535 ( 2^16 — 1 ) символов. See section 6.5.3.1 Молчаливые изменения определений столбцов. See section 6.2.3.2 Типы данных BLOB и TEXT . MEDIUMBLOB MEDIUMTEXT Столбец типа BLOB или TEXT с максимальной длиной 16777215 ( 2^24 — 1 ) символов. See section 6.5.3.1 Молчаливые изменения определений столбцов. See section 6.2.3.2 Типы данных BLOB и TEXT . LONGBLOB LONGTEXT Столбец типа BLOB или TEXT с максимальной длиной 4294967295 ( 2^32 — 1 ) символов. See section 6.5.3.1 Молчаливые изменения определений столбцов. Следует учитывать, что в настоящее время протокол передачи данных сервер/клиент и таблицы MyISAM имеют ограничение 16 Мб на передаваемый пакет/строку таблицы, поэтому пока нельзя использовать этот тип данных в его полном диапазоне. See section 6.2.3.2 Типы данных BLOB и TEXT . ENUM(‘значение1′,’значение2’. ) Перечисление. Перечисляемый тип данных. Объект строки может иметь только одно значение, выбранное из заданного списка величин ‘значение1’ , ‘значение2’ , . , NULL или специальная величина ошибки «» . Список ENUM может содержать максимум 65535 различных величин. See section 6.2.3.3 Тип перечисления ENUM . SET(‘значение1′,’значение2’. ) Набор. Объект строки может иметь ноль или более значений, каждое из которых должно быть выбрано из заданного списка величин ‘значение1’ , ‘значение2’ , . Список SET может содержать максимум 64 элемента. See section 6.2.3.4 Тип множества SET .

Типы данных MySQL

Введение

В данном пособии описаны методы выполнения основных действий с базами данных в среде MySQL. Рассмотрен синтаксис основных SQL-инструкций для создания и сопровождения реляционной базы данных. Приведены примеры управления привилегиями пользователей по работе с табличными данными. Для отладки и демонстрации примеров был использован сервер MySQL версии 5.0.1.

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

При написании учебного пособия в качестве базового источника сведений была выбрана книга [ 1 ] , т.к. в данной книге, по мнению авторов, необходимый для изучения материал представлен, во-первых, достаточно полно и, во-вторых, без излишнего нагромождения несущественных для первого ознакомления деталей.

Типы данных MySQL

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

Все типы данных, с которыми работает MySQL, можно разбить на три группы: числовые, текстовые и даты-времени. Рассмотрим эти типы данных по порядку.

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

Числовые типы столбцов используются для хранения чисел, все числовые типы можно разбить на два подтипа: для хранения точных чисел и чисел с плавающей точкой. Все числовые типы характеризуются длиной хранимых чисел, а типы с плавающей точкой еще и числом десятичных разрядов. Эти значения указываются после объявления типа столбца, например, FLOAT(10, 2). В примере указано, что запись числа содержит 10 символов и два знака после десятичного разделителя.

Объявление числовых типов можно заканчивать ключевыми словами ZEROFILL и (или) USIGNED. Ключевое слово USIGNED означает, что столбец содержит только положительные числа или нули. ZEROFILL означает, что число будет отображено с ведущими нулями.

Типы данных NUMERIC и DECIMAL (DEC) идентичны. Эти типы данных используются для хранения чисел с плавающей точкой. Обычно их используют для хранения денежных значений.

Тип данных INTEGER можно сократить до INT. Это означает целое число в заданном диапазоне. Для хранения этого типа данных отводится 4 байта, что соответствует числам от -2 147 483 648 до 2 147 483 647. Также существует несколько вариантов типа INTEGER.

Тип данных TINYINT используется для однобайтных чисел от -128 до 127. Для хранения двухбайтных чисел (от -32 768 до 32 767) используется тип данных SMALLINT, а для хранения самых больших целых чисел (от -2 63 до 2 63 -1) используется тип BIGINT .

Числа с плавающей точкой имеют либо тип FLOAT (четырехбайтные числа в диапазоне от -3.4 * 10 38 до 3.4 * 10 38 ), либо тип DOUBLE (восьмибайтные числа в диапазоне от -10 -308 до 10 308 )

1.2. Текстовые типы данных

В типах данных char и varchar хранятся данные, состоящие из следующих компонентов:

— знаки в верхнем и нижнем регистре, например a, b и C.

— цифры, например 1, 2 и 3.

— специальные знаки, например символы @, & !.

Тип CHAR используется для хранения строк фиксированной длины. После ключевого слова CHAR обычно указывается длина строки, например, CHAR(50). Если длина строки не указана, то считается, что длина равна одному символу. Максимальная длина поля данного типа равняется 255 символам. Если число переданных в строку символов меньше указанной длины, то строка будет дополнена пробелами, если больше, то обрезана. При возврате значения пробелы будут удалены из строки.

Тип VARCHAR предназначен для хранения строк переменной длины. Как и в предыдущем типе данных задается максимальная длина строки, например, VARCHAR(30). При этом, более длинные строки, переданные в этот столбец, будут обрезаны.

Разница двух описанных типов заключается в том, что по строкам с фиксированной длиной выборка идет намного быстрее. И если важна скорость работы с базой данных, то предпочтительно выбирать именно тип CHAR.

Тип полей TEXT используется для хранения более длинных фрагментов текста, чем допускается предыдущими типами. Тип BLOB означает большой двоичный объект. Эти два типа одинаковы за исключением того, что в типе BLOB сравнение строк идет с учетом регистра символов, а в типе TEXT без учета регистра. Оба типа имеют переменную длину и оба имеют некоторые вариации:

— TINYTEXT и TINYBLOB могут хранить до 255 символов;

— TEXT и BLOB могут хранить до 64 килобайт информации;

— MEDIUMTEXT и MEDIUMBLOBмогут хранить до 16 мегабайт;

— LONGTEXT и LONGBLOB могут хранить до 4 гигабайт.

Тип ENUM позволяет перечислить набор возможных значений для ввода в поле и хранит только одно значение из представленного списка. Например, ENUM(`m`,`a`,`z`) . Если не указать, какое значение используется в поле по умолчанию, то будет использовано первое значение списка.

Тип SET аналогичен типу ENUM, но позволяет хранить несколько значений из списка значений в поле.

1.3. Типы даты и времени

Тип DATE применяется для хранения дат в формате ( гггг-мм-дд ), а тип TIME применяется для хранения времени в формате (чч:мм:сс).

Тип DATETIME объединяет два предыдущих типа и имеет формат (гггг-мм-дд чч:мм:сс ) .

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

Поле типа YEAR содержит значение года. Возможны две длины: YEAR(2) и YEAR(4) для двух, и четырех цифр года соответственно. При использовании YEAR(2) диапазон дат принимается с 1970 по 2069 годы.

Не нашли то, что искали? Воспользуйтесь поиском:

Лучшие изречения: При сдаче лабораторной работы, студент делает вид, что все знает; преподаватель делает вид, что верит ему. 9324 — | 7291 — или читать все.

91.105.232.77 © studopedia.ru Не является автором материалов, которые размещены. Но предоставляет возможность бесплатного использования. Есть нарушение авторского права? Напишите нам | Обратная связь.

Отключите adBlock!
и обновите страницу (F5)
очень нужно

Выбор типов данных в Mysql

При создании таблиц в Mysql мы определяем типы данных и дополнительные правила для колонок (размер, индексы, ограничения):

# Пример создания таблицы в Mysql

Как выбрать «правильные» типы данных? Очень просто — нужно соответствовать правилу » чем меньше тем лучше «. Чем меньше места будут занимать значения в таблице, тем проще будет базе данных читать и записывать их.

Нужны ли все колонки?

Для начала задайте своему приложению пару вопросов. Нужны ли все колонки? Возможно хватит только некоторых?

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

Какая самая короткая версия данных?

Стоит ли хранить пол (gender) пользователя в полную длину? Или достаточно будет одной буквы (f/m)? Стоит ли хранить телефон пользователя с кодом страны, либо достаточно будет только прямого номера?

Сократите длину всех колонок до минимума

Задайте эти вопросы всем колонкам будущей таблицы.

NULL значения

Значение NULL в Mysql — это специальное значение. Для работы с ним предусмотрены специальные функции. Для его обработки нужна дополнительная логика. Хорошим правилом будет избегать использования этого значения. Вместо этого можно использовать пустые значения для строк либо нули для чисел:

# Значение NULL использоваться не будет

Однако не воспринимайте это, как ограничение. В некоторых случаях удобно использовать NULL, чтобы обозначить отсутсвие значения. Например, в DATETIME колонках:

Целые числа

Для всех числовых колонок обязательно рассчитайте максимальное значение. В Mysql существует 4 целочисленных типа:

• TINYINT : 8 бит, максимум 127
• SMALLINT : 16 бит, максимум 32 676
• INT : 32 бит, максимум 2 x 10 9
• BIGINT : 64 бит, максимум 9 x 10 18

Представьте, что вы используете тип INT для колонки, в которой хранится возраст пользователя. Тогда, как вам достаточно типа TINYINT , вы используете на 32 — 8 = 24 бита больше. Для каждой строки. Если у Вас 10 тыс. пользователей, вы зря расходуете: 24/8 * 10 000 = 30 Кб . Если пользователей 10 млн, то 30 Мб .

Выбирайте минимальный тип данных исходя из максимального значения колонки.

Это может быть не так много для диска, зато критично для оперативной памяти.

UNSIGNED

Если отрицательное число неактуально для колонки, используйте UNSIGNED значения. Тогда максимально значение будет в два раза больше, однако минимальным будет ноль:

• UNSIGNED TINYINT : 8 бит, максимум 255
• UNSIGNED SMALLINT : 16 бит, максимум 65 535
• UNSIGNED INT : 32 бит, максимум 4 x 10 9
• UNSIGNED BIGINT : 64 бит, максимум 18 x 10 18

Длинна числовых типов

В Mysql можно указать длину колонки после указания числового типа:

Это не имеет никакого влияния ни на размер колонки ни на максимальное число. Просто никогда не используйте длину для числовых типов.

Большие числа

Для хранения очень больших точных чисел Mysql предлагает использовать тип DECIMAL :

# Использование DECIMAL для нецелого числа

В скобках указываются количество количество цифр всего и их количество после запятой (может быть нулем). Поскольку процессоры не поддерживают математических операций с подобными числами, Mysql все подсчеты делает на своей стороне. А значит, это очень медленно .

FLOAT / DOUBLE

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

Используйте 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 символов. Точно также, при создании индекса по этой колонке необходимо указать длину:

# Указываем длину колонки для индексации

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

# По колонке body_md5 можно создать уникальный индекс

Значения строк может быть одним из заранее известных значений (например, список стран). Если список фиксирован (новые страны не появляются каждый день), удобно будет использовать тип ENUM . Он позволяет задать определенный список значений и использовать только их в строках таблицы.

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

Не используйте этот тип для динамических значений . Если есть список значений, который может расширяться (например, категории товаров), используйте отдельную таблицу с идентификатором:

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

DATETIME / TIMESTAMP

Оба формата дат позволяют хранить значения даты и времени вплоть до секунд. Однако между ними есть отличия:

• DATETIME занимает 8 байт и позволяет хранить даты с 1001 года до 9999 года.
• TIMESTAMP занимает 4 байта и позволяет хранить даты с 1970 года до 2038 года.

Используйте формат TIMESTAMP для простановки дат событий (для чего он и создан). Например, время регистрации пользователя или публикации комментария. К тому же, он имеет удобный механизм инициализации и обновления:

# Инициализация и автообновление колонок TIMESTAMP

В остальных случаях используйте DATETIME .

TL;DR версия

• Удалите ненужные колонки из схемы.
• Сократите длину колонок до минимума.
• Избегайте использования NULL значений.
• Выберите минимально необходимые числовые типы ( TINYINT / SMALLINT вместо INT ).
• Используйте FLOAT / DOUBLE вместо DECIMAL для приблизительных чисел.
• Выберите CHAR для строк приблизительно одинаковой длины.
• Для остальных строк — выберите VARCHAR .
• Не используйте TEXT / BLOB для сортировки и индексирования.
• Используйте ENUM вместо строк из фиксированного набора (например, списка стран).
• Используйте TIMESTAMP для простановки времени событий (регистрация, отправка сообщения и т.п.).
• Для остальных дат используйте DATETIME .
• Прочитайте об устройстве индексов и тюнинге настроек в Mysql.

Ускорение репликации в Mysql 5.6+

Сравнение Vertica и Mysql на практике

Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL

Check-unused-keys для определения неиспользуемых индексов в базе данных

Повышение скорости работы запросов с MySQL Handlersocket

Как восстановить данные, если MySQL упал и не поднимается

Что такое индексы в Mysql и как их использовать для оптимизации запросов

Правильная настройка Mysql под нагрузки и не только. Обновлено.

Анализ медленных запросов с помощью EXPLAIN

Правильный поиск по тексту в Mysql (full-text search)

Синтаксис и оптимизация Mysql LIMIT

Оптимизация постраничного вывода данных

Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit

3 примера установки индексов в JOIN запросах

Использование партиций для ускорения сложных удалений

Быстрая альтернатива Mysqldump для больших таблиц без блокировок и выключений.

Сравнение двух движков и когда стоит использовать каждый из них

Эффективная замена ORDER BY RAND()

Рекомендации по настройке Redis для оптимизации ресурсов и повышения стабильности на производственном сервере

Настройки для улучшения производительности Postgres

Как устроена распределенная база данных на основе blockchain механизма

Что делать, если во время репликации встречается: Error ‘Duplicate entry ‘115846’ for key ‘PRIMARY» on query. Default database: ‘db’. Query: ‘INSERT INTO some_table .

Реализация поиска по части слова на основе Sphinx

И как правильно работать с длительными соединениями в MySQL

Типы данных в MySQL (сжатый справочник для PHP программиста)

Что и как и как много хранит MySQL?

Данный материал создан специально для программистов, которые быстро смогут определиться какой тип данных лучше выбрать для хранения значений в БД MySQL.

Для затравки, интересная цитата из мануала по MySQL:
«Максимальный размер записи в MyISAM составляет 65534 байтов. Каждый BLOB или TEXT-столбец засчитывается здесь как 5-9 байтов.» — как это трактовать однозначно не понятно. НО ясно что много-примного столбцов в таблицу на засунешь. «Извращенистые программисты» — будьте аккуратны (66000 столбцов вы точно создать не сможете).

UPD: Если найдете ошибку, или я что-то где-то утаил — отпишитесь в комментах, с удовольствием добавлю.

UPD1 В первую очередь (и самый лучший вариант для новичков) прочитать ОФИЦИАЛЬНЫЙ МАНУАЛ dev.mysql.com/doc/refman/5.0/en/data-types.html (спасибо Psyh за прямую ссылку), а здесь вырезка для META обработчиков данных (как в лице программистов так и в лице машинной обработки).

UPD2 В принципе, все что написано ниже, можно прочитать по адресу www.mysql.ru/docs/man/Column_types.html (за ссылку «русского перевода», спасибо artuska).

UPD3 Еще одну неплохую ссылку предоставил 4all: newcontinent.ru/h/mysqlc (материал на русском)

UPD4 Цитата из комментов от egorF:
# 14«Как главный редактор русскоязычного перевода доки на MySQL, я рекомендую в него не заглядывать — он уже сказочно морально устарел.»

Следующий массив вполне понятен PHP программистам.
Да и вообще, любые уважающие себя программисты это поймут.

Например:
‘int’=>Array(‘byte’=>4, ‘min’=>-2147483648, ‘max’=>2147483647, ‘umin’=>0, ‘umax’=>4294967295),

Трактуется следующим образом:
Тип поля INT использует 4 байта для хранения значения. Минимально значение -2147483648, максимальное 2147483647. Беззнаковый INT (unsigned) хранит значения от 0 до 4294967295.

Используется так:
Мне надо сохранить в поле максимальное числовое значение 234 259 000 000 000.
INT — для этого не подходит. Смотрим другие типы и находим, что BIGINT вполне подойдет.

$MYSQL_TYPES=Array(
// INTEGER
// byte — кол-во байт на хранение,
// max/min — предельные значения,
// umax/umin — беззнаковые предельные значения
‘int’ =>Array( ‘byte’ =>4, ‘min’ =>-2147483648, ‘max’ =>2147483647, ‘umin’ =>0, ‘umax’ =>4294967295),
‘bigint’ =>Array( ‘byte’ =>8, ‘min’ =>-9223372036854775808, ‘max’ =>9223372036854775807, ‘umin’ =>0, ‘umax’ =>18446744073709551615),
‘tinyint’ =>Array( ‘byte’ =>1, ‘min’ =>-128, ‘max’ =>127, ‘umin’ =>0, ‘umax’ =>255),
‘smallint’ =>Array( ‘byte’ =>2, ‘min’ =>-32768, ‘max’ =>32767, ‘umin’ =>0, ‘umax’ =>65535),
‘mediumint’ =>Array( ‘byte’ =>3, ‘min’ =>-8388608, ‘max’ =>8388607, ‘umin’ =>0, ‘umax’ =>16777215),

// DECIMAL DECIMAL(M,D) m — кол-во цифр (max 65 цифр), d — сколько из них могут быть после запятой
// min_byte/max_byte — краевые значения размера поля в байтах,
// byte_formula — формула вычисления размерности
// length — максимальное кол-во цифр
‘decimal’ =>Array( ‘min_byte’ =>2, ‘max_byte’ =>67, ‘byte_formula’ => ‘(D==0?(M+1):(M+2)’ , ‘length’ =>65),
‘dec’ =>Array( ‘min_byte’ =>2, ‘max_byte’ =>67, ‘byte_formula’ => ‘D==0?(M+1):(M+2)’ , ‘length’ =>65),
‘numeric’ =>Array( ‘min_byte’ =>2, ‘max_byte’ =>67, ‘byte_formula’ => ‘D==0?(M+1):(M+2)’ , ‘length’ =>65),

// FLOAT DOUBLE
// Внимание! Не храните денежные значения в этих полях. Деньги надо хранить — в DECIMAL
// у FLOAT ТОЧНОСТЬ ТОЛЬКО 7 ЦИФР. (все остальные цифры «смазываются»)
// у DOUBLE ТОЧНОСТЬ ТОЛЬКО 15 ЦИФР. (все остальные цифры «смазываются»)
// byte — кол-во байт для хранения поля (по-умолчанию)
// max_byte — максимальное кол-во байт для хранения
// negative_min/negative_max — минмаксы для отрицательных чисел
// positive_min/positive_max — минмаксы для положительных чисел
‘float’ =>Array( ‘byte’ =>4, ‘max_byte’ =>8, ‘negative_min’ =>-3.402823466E+38, ‘negative_max’ =>-1.175494351E-38, ‘positive_min’ =>1.175494351E-38, ‘positive_max’ =>3.402823466E+38),
‘double’ =>Array( ‘byte’ =>8, ‘negative_min’ =>-1.7976931348623157E+308, ‘negative_max’ =>-2.2250738585072014E-308, ‘positive_min’ =>2.2250738585072014E-308, ‘positive_max’ =>1.7976931348623157E+308),

// BOOLEAN
// сами все поймете
‘bool’ =>Array( ‘byte’ =>1, ‘true’ =>1, ‘false’ =>0),
‘boolean’ =>Array( ‘byte’ =>1, ‘true’ =>1, ‘false’ =>0),

// VARCHAR
// byte — кол-во байт отведенных для хранения (можно задать меньше)
// min_byte — минимальное кол-во байт в которых может храниться поле (если длина равна 1)
// В MYSQL 5.0.3 и выше, VARCHAR может быть до 65,535 символов.
// length — максимальная длина символов в поле
‘varchar’ =>Array( ‘byte’ =>256, ‘min_byte’ =>2, ‘length’ =>255),
‘char’ =>Array( ‘byte’ =>256, ‘min_byte’ =>2, ‘length’ =>255),

// TEXT
// byte — кол-во байт для хранения поля
// min_byte — минимальное кол-во байт для хранения одного символа (если длина поля равна 1)
// length — максимальное количество символов в поле
‘tinytext’ =>Array( ‘byte’ =>256, ‘min_byte’ =>2, ‘length’ =>255),
‘text’ =>Array( ‘byte’ =>65537, ‘min_byte’ =>3, ‘length’ =>65535),
‘mediumtext’ =>Array( ‘byte’ =>16777218, ‘min_byte’ =>4, ‘length’ =>16777215),
‘longtext’ =>Array( ‘byte’ =>4294967300, ‘min_byte’ =>5, ‘length’ =>4294967296),
‘tinyblob’ =>Array( ‘byte’ =>256, ‘min_byte’ =>2, ‘length’ =>255),
‘blob’ =>Array( ‘byte’ =>65537, ‘min_byte’ =>3, ‘length’ =>65535),
‘mediumblob’ =>Array( ‘byte’ =>16777219, ‘min_byte’ =>4, ‘length’ =>16777215),
‘longblob’ =>Array( ‘byte’ =>4294967300, ‘min_byte’ =>5, ‘length’ =>4294967296),

// DATETIME
// byte — кол-во байт для хранения значения поля
// mask — стандартная маска ввода значения (есть куча других вариантов, о них читайте в мануале)
// min/max — минимальные максимальные значения дат которые сохраняют поля
‘datetime’ =>Array( ‘byte’ =>8, ‘mask’ => ‘YYYY-MM-DD HH:MM:SS’ , ‘min’ => ‘1000-01-01 00:00:00’ , ‘max’ => ‘9999-12-31 23:59:59’ ),
‘date’ =>Array( ‘byte’ =>3, ‘mask’ => ‘YYYY-MM-DD’ , ‘min’ => ‘1000-01-01’ , ‘max’ => ‘9999-12-31’ ),
‘time’ =>Array( ‘byte’ =>3, ‘min’ => ‘-838:59:59’ , ‘max’ => ‘838:59:59’ ),
‘year’ =>Array( ‘byte’ =>1, ‘min’ =>1901, ‘max’ =>2155),
‘timestamp’ =>Array( ‘byte’ =>4, ‘mask’ =>Array(14=> ‘YYYYMMDDHHMMSS’ ,12=> ‘YYMMDDHHMMSS’ ,10=> ‘YYMMDDHHMM’ ,8=> ‘YYYYMMDD’ ,6=> ‘YYMMDD’ ,4=> ‘YYMM’ ,2=> ‘YY’ ), ‘min’ =>1970, ‘max’ =>2036 ),

// ENUM
// byte — кол-во байт на хранение поля
// max_byte — максимальное кол-во байт, которое можно достигнуть при максимальном кол-ве элементов
// max_number_of_element — кол-во элементов, которое может содержать поле
‘enum’ =>Array( ‘byte’ =>1, ‘max_byte’ =>2, ‘max_number_of_element’ =>65535),
‘set’ =>Array( ‘byte’ =>1, ‘max_byte’ =>8, ‘max_number_of_element’ =>64)
); * This source code was highlighted with Source Code Highlighter .

Урок 5. MySQL. Типы данных. Часть 2

Освойте бесплатно наиболее простой, быстрый и гибкий способ создавать адаптивные веб-сайты.

Дизайн лендинга

Создавайте дизайн любых сайтов — для себя и на заказ!

Популярное

• Главная
• ->
• Материалы
• ->
• БД MySQL (типы данных, таблицы, запросы, триггеры, индексы)

Reg.ru: домены и хостинг

Крупнейший регистратор и хостинг-провайдер в России.

Более 2 миллионов доменных имен на обслуживании.

Продвижение, почта для домена, решения для бизнеса.

Более 700 тыс. клиентов по всему миру уже сделали свой выбор.

Бесплатный Курс «Практика HTML5 и CSS3»

Освойте бесплатно пошаговый видеокурс

по основам адаптивной верстки

на HTML5 и CSS3 с полного нуля.

Фреймворк Bootstrap: быстрая адаптивная вёрстка

Пошаговый видеокурс по основам адаптивной верстки в фреймворке Bootstrap.

Научитесь верстать просто, быстро и качественно, используя мощный и практичный инструмент.

Верстайте на заказ и получайте деньги.

Что нужно знать для создания PHP-сайтов?

Ответ здесь. Только самое важное и полезное для начинающего веб-разработчика.

Узнайте, как создавать качественные сайты на PHP всего за 2 часа и 27 минут!

Создайте свой сайт за 3 часа и 30 минут.

После просмотра данного видеокурса у Вас на компьютере будет готовый к использованию сайт, который Вы сделали сами.

Вам останется лишь наполнить его нужной информацией и изменить дизайн (по желанию).

Изучите основы HTML и CSS менее чем за 4 часа.

После просмотра данного видеокурса Вы перестанете с ужасом смотреть на HTML-код и будете понимать, как он работает.

Вы сможете создать свои первые HTML-страницы и придать им нужный вид с помощью CSS.

Бесплатный курс «Сайт на WordPress»

Хотите освоить CMS WordPress?

Получите уроки по дизайну и верстке сайта на WordPress.

Научитесь работать с темами и нарезать макет.

Бесплатный видеокурс по рисованию дизайна сайта, его верстке и установке на CMS WordPress!

Хотите изучить JavaScript, но не знаете, как подступиться?

После прохождения видеокурса Вы освоите базовые моменты работы с JavaScript.

Развеются мифы о сложности работы с этим языком, и Вы будете готовы изучать JavaScript на более серьезном уровне.

*Наведите курсор мыши для приостановки прокрутки.

БД MySQL (типы данных, таблицы, запросы, триггеры, индексы)

Введение

Довольно часто, создавая или заказывая какой-либо ИТ-проект, мы слышим о базах данных.

Что же такое база данных? Для чего она нужна? Как ей пользоваться? На эти и другие вопросы я постараюсь ответить в данном курсе статей о базах данных.

Все аспекты я постараюсь описать примерами. Итак, начнем.

БД — совокупность данных, систематизированных таким образом, чтобы получить их было просто и удобно.

СУБД — система управления базами данных, совокупность функций и процедур для работы с БД.

Существует великое множество СУБД. Для web-разработки, как правило, используется СУБД MySQL.

Почему именно она? Это простая, бесплатная и довольно мощная система.

Для примера можно взять Facebook. Данные хранятся там именно в MySQL. MySQL оптимизирована для получения данных из БД. Практически на всех сайтах мы в основном читаем данные из БД, прежде чем отправить их на страницу.

Исходя из всех преимуществ СУБД MySQL, все примеры будут указаны в кодах этой СУБД.

Установка СУБД

Для начала установим MySQL. Проще всего это сделать установив т.н. Джентльменский набор веб-разработчика. Дистрибутив и порядок установки вы можете найти на сайте http://www.denwer.ru/.

Также вы можете изучить следующий материал, посвященный основам работы с Денвером.

Для работы с БД из всего набора Денвера нам нужна только утилита phpMyAdmin. Запускаем ее в браузере по адресу http://localhost/Tools/phpMyAdmin/index.php

Для начала создадим БД, с которой будем работать. Для этого на вкладке Базы данных (выбрана на рисунке) укажем реквизиты новой БД. Назовем ее test.

Типы данных

В MySQL, как и в других средах разработки, различают несколько типов данных. Изучая СУБД, в первую очередь необходимо изучить все типы данных. В общем, они идентичны во всех СУБД, но у каждой есть свои особенности.

1) INT — Целочисленный формат. В основном используется для идентификаторов и переключателей (0 — нет, 1 — есть).

2) CHAR – Текстовый формат. Ограничен 32 тыс. символов. Используется для хранения небольшого объема текстовой информации. Пример CHAR(128).

3) TEXT — Текстовый формат. Практически неограничен. Используется для хранения большого объема текстовой информации.

4) DATE – Дата.

5) DATETIME – Дата и время.

6) DECIMAL – Числовой формат с разделителем. Пример DECIMAL(14, 2) позволит нам записать число, длинной 14 символов с двумя знаками после запятой.

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

Таблицы

Основная сущность БД это таблица. Остальные, как правило, вспомогательные.

Таблица — некоторая группа данных, объединенная по каким-либо общим свойствам. Например, список товаров на складе или список пользователей системы. Такие таблицы при проектировании приложения зовутся справочниками.

Роль таблицы в БД — хранение структурированного набора данных и только. В MySQL таблица представляет собой стандартную таблицу с колонками и строками.

Строки указывают на запись, колонки указывают на реквизиты записи.

Рассмотрим пример. Создадим таблицу «Users» (справочник пользователей) с помощью следующего кода:

Старайтесь работать с БД именно с помощью скриптов. Это упростит понимание команд и функций. Кроме того, если у вас нет доступа к среде разработки (phpMyAdmin), это вам очень поможет. Работая со скриптами, используем вкладку SQL (см. рис. ниже)

ВНИМАНИЕ! При создании таблицы старайтесь называть ее в соответствии с данными, которые будут в ней храниться. Также в каждой таблице желательно создавать поле идентификатора записи (ID). Это очень пригодится.

Для удаления таблицы используется код:

Для множественного удаления

Будьте внимательны при вызове указанных команд.

Для изменения таблицы используется код:

Пока на этом остановимся.

Запросы

БД, как таковая, должна иметь механизмы ввода-вывода информации. Для вставки, изменения, получения или удаления необходимо использовать запросы. В данном курсе я опишу только запросы SQL (Structured Query Language — «Структурированный язык запросов»), т.к. большинство БД работают именно с запросами такого типа.

Существует 4 вида запросов:

Для примера, поиграем с нашим справочником «Users»:

1) Select — Получение данных из таблицы

Давайте разберем запрос. SELECT[Выбрать] *[Все столбцы] FROM[Из таблицы] Users. Данный запрос вернет все записи по всем столбцам таблицы.

Чтобы ограничить выборку (полные выборки требуются только в случае получения отчетов, и то не всегда), добавим в наш запрос условие WHERE.

Здесь мы получаем данные по пользователю №1. Вроде бы все просто, не правда ли? Идем далее..

2) Update — Обновление данных в таблице

Давайте разберем запрос. UPDATE[Обновить] Users SET[Установить [поле] user_name = ‘Иванов И.И.’ WHERE[Где] user_ .

3) Insert — Вставка новых данных в таблицу

По хорошему, данный запрос нужно писать в первую очередь, т.к. справочник у нас изначально пуст, но обычно разработчики создают таблицы через интерфейс (phpMyAdmin) и сразу добавляют несколько тестовых записей.

Разбираем запрос. INSERT INTO[Вставить в] Users ([перечнь полей]user_id, user_name) VALUES ([Перечнь значений]2, ‘Петров П.П.’). Запрос вставит запись в таблицу со значениями user_id равному 2 и user_name равному ‘Петров П.П.’.

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

Однако при этом важно соблюдать порядок столбцов.

При вставке нескольких строк можно воспользоваться запросом множественной вставки.

4) Delete — Удаление данных из таблицы

Тут все просто. DELETE[Удалить] *[Все] FROM[Из] Users.

Запросу неважно, что будет указано после команды DELETE, будь это перечень полей или одно поле, он удалит всю строку данных.

ВНИМАНИЕ! В данном запросе не забывайте писать условие WHERE (Это касается и запроса UPDATE), иначе запрос очистит всю вашу таблицу.

При отладке приложения старайтесь при написании команды DELETE изначально описывать запрос в виде SELECT. Нужно нам, например, написать запрос на удаление одного пользователя, мы пишем:

Проверяем. Запрос выводит одну строку, которую нам надо удалить. Заменяем теперь SELECT на DELETE и спокойно выполняем запрос, не опасаясь за данные.

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

Пример вставки строк в таблицу. Среда оповещает нас о вставке строки.

Триггеры

Триггер — процедура, выполняемая при перед/после выполнения запросов вставки, обновления или удаления.

Зачем нам нужен триггер? Триггер позволяет нам сократить код приложения при написании проверок или дополнительных действий. Также триггер помогает проверять связи при редактировании записей, но об этом позже.

Рассмотрим на примере.

Допустим мы желаем хранить историю изменений в нашем приложении. Иногда это очень полезно. Создадим таблицу истории. Как правильно описать поля, мы выяснили ранее.

Определим типы изменений:

1 — добавление записи
2 — изменение записи

Теперь создадим триггер на нашей таблице Users:

Теперь при добавлении нового пользователя в таблицу истории триггер будет добавлять запись о его создании. По сути, триггеры очень полезны. Проверка может осуществляться на уровне БД, не касаясь приложения.

В некоторых СУБД триггеры используются для заполнения уникального идентификатора таблиц, но в MySQL это не требуется.

Индексы

Индексы — это наборы данных в БД, используемые для оптимизации поиска записей.

В таблицах данные хранятся в случайном порядке и, иногда, запись перебором (если индекса нет) найти очень затратно по времени. Индекс содержит в себе указатели на группы данных в таблице, объединенные по какому-либо критерию.

Рассмотрим на примере нашего справочника. Допустим, мы хотим привязать сотрудников по отделам. Модифицируем наш справочник пользователей, добавив в него ссылку на группу.

Теперь у нас справочник пользователей имеет привязку к группе. Заполним пустые поля записями 1 и 2. При выполнении запроса вида:

будут проверяться все записи в таблице на предмет равенства поля group_id_ref единице.

Для ускорения поиска необходимо добавить индекс.

ВИМАНИЕ! Эта процедура разовая и проводится на этапе разработки. Времени много не занимает, поэтому советую не опускать этот пункт. Кроме того, это основа быстродействия вашего приложения.

Теперь разберем, чем нам поможет данных индекс. Возьмем тот же запрос:

Алгоритм поиска по таблице будет идти только по группе записей, group_id_ref которых равен 1.

В нашем случае, когда у пользователей только две группы, время поиска сократится незначительно, но в ситуации, когда групп пользователей много, индекс ускоряет поиск в разы и десятки раз.

Теперь, чтобы как-то определить группы, создадим справочник.

Также нам может пригодиться первичный индекс. Для чего он нужен? Для того, чтобы при добавлении записей в справочники каждая из них имела свой идентификатор.

Это очень удобно. Оперируя данными таблицы, нам достаточно указать идентификатор конкретной строки для ее определения. Как пример, для сравнения представим ситуацию, что в нашей таблице Users есть три записи о пользователях.

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

При отсутствии уникального идентификатора запрос на изменение второй записи выглядел бы так:

При наличии уникального идентификатора user_id запрос выглядит немного проще:

При всем при этом поиск в таблице по тексту — не самое лучшее решение. СУБД позволяет это делать, но вы сильно теряете в скорости. Недостатком такого поиска также является передача в запрос текстовых параметров. Их необходимо экранировать.

В MySQL есть очень простой способ создания уникального индекса.

Рассмотрим подробнее код:

ВНИМАНИЕ! ALTER это изменение структуры БД. Необходимо минимизировать или вообще стараться избегать этой команды в скриптах. Все индексы и счетчики должны быть учтены при проектировке БД.

Вот так выглядит скрипт создания таблицы Users без использования ALTER:

Итог

В данной статье мы познакомились с Базами данных, узнали о таблицах, запросах, триггерах и индексах. Пройдемся еще раз по основным объектам.

1) Таблица — объект БД для хранения данных.
2) Запрос — средство ввода-вывода информации.
3) Триггер — процедура, выполняемая при изменении данных в таблице.
4) Индекс — указатель на записи в таблице.

По сути данного набора знаний будет вполне достаточно, чтобы разработать сайт-визитку.

Материал подготовил Владимир Миняйлов специально для сайта CodeHarmony.ru

Исходники:

P.S. Хотите углубить свои знания и навыки? Присмотритесь к премиум-урокам по различным аспектам сайтостроения, включая SQL и работу с БД, а также к бесплатному курсу по созданию своей CMS-системы на PHP с нуля.

Понравился материал и хотите отблагодарить?
Просто поделитесь с друзьями и коллегами!

Tarlyun blog

Хранение не целых чисел в MySQL

В MySQL существует множество типов данных для хранения чисел как целых, так и с плавающей точкой.
Рассмотрим существующие форматы:

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

Для чисел с плавающей точкой используются: FLOAT, DOUBLE. Главный минус чисел с плавающей точкой (или вещественных чисел) – принцип их представления и хранения. Такие числа хранятся как степени двойки. Так как не все десятичные числа имеют точное представление в виде двоичной дроби, результаты хранения получаются округленными. Даже при элементарных математических операциях возможны расхождения. Более подробно про это можно прочитать на хабре

При хранении важных данных (к примеру, банковский счет) важен каждый символ даже в последнем разряде после точки, поэтому использовать числа с плавающей точкой нельзя.
Избежать проблем хранения и поиска данных можно при помощи типов: DECIMAL, NUMERIC.

До версии 5.03 эти типы хранились как строки, при этом использовалось по одному символу строки для каждого разряда числа (при необходимости, тратились дополнительно по одному символу на знак числа и на десятичный разделитель), также была возможна потеря точности из-за некорректной реализации арифметики таких чисел.

Начиная с 5.03, в MySQL появилась новая библиотека для арифметики чисел с фиксированной точкой и изменился подход к хранению таких чисел. Теперь целая часть и часть после точки хранятся как 2 отдельных, целых числа. Оценить расход места можно на основании следующей таблицы:

Оставшиеся цифры	Число байт
1-2	1
3-4	2
5-6	3
7-9	4

Расчет ведется очень просто: к примеру, нам надо сохранить DECIMAL(10,2) – целая часть имеет 8 цифр и занимает 4 байта, часть после точки – 2 цифры и занимает 1 байт. Итого на хранение будет потрачено 5 байт.

В ранних версиях MySQL типы DECIMAL и NUMERIC вели себя по-разному. SQL-стандарт требует, чтобы точность NUMERIC(M,D) была точно M цифр. Для DECIMAL(M,D) стандарт требует точности не меньше M цифр, но позволяет хранить больше. Это означает, что если бы вы захотели сохранить число 1,00005 в DECIMAL(6,4) и NUMERIC(6,4), то по стандарту NUMERIC сохранит 1.0000, а DECIMAL может сохранить 1.00005. Такое поведение при большом количестве математических операций может дать небольшую погрешность.

В последних версиях MySQL DECIMAL и NUMERIC оба типа имеют точность ровно M цифр.

Чтобы переварить все вышеизложенное, рассмотрим пару примеров (Server version: 5.0.77 Gentoo Linux).

Создадим таблицу test с 4 полями разных типов. Внесем одинаковые данные и посмотрим, как будет вести себя MySQL

CREATE TABLE test (a FLOAT, b DOUBLE, c DECIMAL (6,4), d NUMERIC(6,4)) ENGINE = MyISAM; INSERT INTO test(a,b,c,d) VALUES (0.3, 0.3, 0.3, 0.3), (0.00003, 0.00003, 0.00003, 0.00003); SELECT a, b, c, d FROM test; +——-+——-+———+———+ | a | b | c | d | +——-+——-+———+———+ | 0.3 | 0.3 | 0.3000 | 0.3000 | | 3e-05 | 3e-05 | 0.0000 | 0.0000 | +——-+——-+———+———+

Наглядный пример, почему не стоит доверять важные данные числам с плавающей точкой. Но не стоит думать, что DOUBLE – панацея. FLOAT и DOUBLE – одинаковы по принципу хранения. Оба типа одинаково не точны, просто в DOUBLE неточности появляются при бОльшем числе знаков.

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

CREATE TABLE test (money FLOAT) ENGINE = MyISAM; INSERT INTO test(money) VALUES (123.34),(8691.00),(0.01); SELECT money, 1+money, 1*money FROM test; +———+——————+———————+ | money | 1+money | 1*money | +———+——————+———————+ | 123.34 | 124.33999633789 | 123.33999633789 | | 8691 | 8692 | 8691 | | 0.01 | 1.0099999997765 | 0.0099999997764826 | +———+——————+———————+

Если я вас еще не убедил отказаться от FLOAT , приготовьтесь к проблемам при поиске.

Проблема с поиском:

SELECT money FROM test WHERE money = 123.34; Empty set (0.00 sec)

Если же у вас назрела необходимость в поиске по полю FLOAT – используйте границы для поиска.

SELECT money FROM test WHERE money > 123.33 AND money

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 6: Типы данных в SQL

Здравствуйте, уважаемые посетители сайта ZametkiNaPolyah.ru. Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В данной публикации мы рассмотрим типы данных в SQL. Хочу обратить ваше внимание на то, что различные СУБД поддерживает различный набор типов данных, поэтому это публикация включает в себя самые часто встречающиеся типы данных в SQL реализации различных СУБД.

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

Типы данных в SQL важно знать, чтобы правильно и грамотно проектировать базы данных, правильно выбранный типа данных в SQL может очень сильно облегчить работу другим разработчикам. Поэтому не советую вам пропускать раздел о типах данных и всегда, когда вы знакомитесь с новой СУБД, обращать внимание на типы данных, которые поддерживает программа и на то, как она эти типы данных обрабатывает.

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

Рассмотрим типы данных, которые есть в языке SQL. У каждого столбца таблицы (у атрибута) должен быть тип данных для значений, которые хранятся в столбце. Тип данных для столбца определяется при создании таблицы, а еще лучше, когда он определяется на этапе проектирования баз данных.

Язык SQL делит данные на пять типов:

1. Целочисленный тип данных SQL.
2. Вещественный тип данных SQL.
3. Типы данных даты и времени SQL.
4. Строковый тип данных SQL.
5. Строковый тип данных SQL в кодировки Юникод.

Давайте посмотрим, что включает в себя каждый из типов данных SQL и какие значения они позволяют нам хранить.

Целочисленный тип данных SQL

Целочисленный тип данных в SQL довольно таки широкий и зависит от реализации СУБД, снизу в таблицы лишь некоторые типичные примеры целочисленного типа данных SQL.

Тип данных SQL	от	до
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint		255
bit		1
decimal	-10^38 +1	10^38 -1
numeric	-10^38 +1	10^38 -1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

Еще раз повторюсь, что набор целочисленных типов данных в SQL зависит целиком и полностью от СУБД.

Вещественный тип данных SQL

Вещественный тип данных SQL, как и целочисленный тип данных, целиком и полностью зависит от реализации СУБД.

Тип	от	до
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

Тип данных даты и времени

Обычно в различных СУБД дата и время — это отдельно выделенный тип данных

Тип данных SQL	от	до
datetime	Jan 1, 1753	Dec 31, 9999
smalldatetime	Jan 1, 1900	Jun 6, 2079
date	Сохраняет дату как June 30, 1991
time	Сохраняет время как 12:30 P.M.

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

Строковых типов данных в SQL может быть гораздо больше, смотрите спецификацию своей СУБД, чтобы узнать какой тип данных может быть использован для строковых значений.

Тип данных SQL	Описание
char	Максимальная длина 8000 символов (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единица измерения SQL типа данных char — символ.
varchar	Максимальная длина 8000 символов (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единица измерения SQL типа данных varchar — символ.
varchar (max)	Максимальная длина 231 символ. Обратите внимание: единица измерения SQL типа данных varchar (max) — символ.
text	Максимальная длина 2,147,483,647 символов. Обратите внимание: единица измерения SQL типа данных text — символ.

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

Да, некоторые СУБД выделяют строковый тип данных в кодировке юникод, поскольку в строках с кодировкой юникод на запись одного символа тратится, как минимум, 8 байт, поэтому мы и выделяем строковый тип данных в SQL с кодировкой юникод.

Тип данных SQL	Описание
nchar	Максимальная длина 4000 символов (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единица измерения SQL типа данных nchar — символ.
nvarchar	Максимальная длина 4000 символов (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единица измерения SQL типа данных nvarchar — символ.
nvarchar (max)	Максимальная длина 231 символ. Обратите внимание: единица измерения SQL типа данных nvarchar (max) — символ.
ntext	Максимальная длина 1,073,741,823 символов. Переменная длина. Обратите внимание: единица измерения SQL типа данных ntext — символ.

Бинарный тип данных в SQL

Бинарный тип данных в SQL используется для хранения информации в бинарном виде (в виде последовательности байт).

Тип данных SQL	Описание
binary	Максимальная длина 8,000 байт (все значения в столбце имеют фиксированный размер, указанный при объявлении столбца). Обратите внимание: единицы измерения типа данных binary — байты.
varbinary	Максимальная длина 8,000 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных varbinary — байты.
varbinary (max)	Максимальная длина 231 байт. Обратите внимание: единицы измерения типа данных varbinary (max) — байты.
image	Максимальная длина 2,147,483,647 байт (все значения в столбце имеют различный размер в зависимости от количества символов, но не более того размера, что был указан при объявлении столбца). Обратите внимание: единицы измерения типа данных image — байты.

Хочу обратить ваше внимание на то, что каждая СУБД поддерживает свой набор типов данных и размерностей, мы рассмотрели базовые типы данных языка SQL, в следующей теме мы рассмотрим типы данных, которые поддерживает SQLite3.

Знаковые и без знаковые типы данных в SQL

Хочу обратить ваше внимание на то, что в некоторых СУБД числовые типы данных могут делиться на знаковые и без знаковые типы данных. И это совершенно разные типы данных.

Например, если у вас в одной таблице хранится id INTEGER UNSIGNED, а в другой id_table1 INTEGER SIGNED, то связь между таблицами по этим двум столбцам вы реализовать не сможете, так как у них разные типы данных. Будьте внимательны и всегда читайте описания типов данных, когда начинаете использовать новую СУБД.

Приведем пример практического применения типов данных SIGNED и UNSIGNED. Например, у MySQL есть тип данных TINYINT, который занимает один байт и прекрасно подходит для хранения возраста, следовательно, в столбец с типом TINYINT можно записывать целые числа от 0 до 256 (два в восьмой степени, в одном байте 8 бит, а один бит может принимать два значения: ноль или единицу), если столбец без знаковый, если столбец знаковый, то в него можно записать числа от -128 до 127 (один бит уйдет на знак).

20 советов по оптимальному использованию MySQL

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

1. Оптимизируйте ваши запросы для кэша запросов.

Большинство серверов MySQL используют кэширование запросов. Это один из эффективных методов улучшения производительности, который выполняется механизмом базы данных в фоновом режиме. Если запрос выполняется много раз, то для получения результата начинает использоваться кэш и операция выполняется значительно быстрее.

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

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE(). Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

2. Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

3. Используйте LIMIT 1, если нужно получить уникальную строку

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

В таком случае добавление LIMIT 1 к вашему запросу может улучшить производительность. При таком условии механизм базы данных останавливает сканирование записей как только найдет одну и не будет проходит по всей таблице или индексу.

4. Индексируйте поля поиска

Индексируйте не только основные и уникальные ключи. Если какие-нибудь столбцы в вашей таблице используются для поисковых запросов, то их нужно индексировать.

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова ( “WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

5. Индексирование и использование одинаковых типов для связываемых столбцов

Если ваше приложение содержит много запросов с директивой JOIN, вам нужно индексировать столбцы, которые связываются в обеих таблицах. Это оказывает эффект на внутреннюю оптимизацию операций связывания в MySQL.

Также связываемые столбцы должны иметь одинаковый тип. Например, если вы связываете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать индекс по крайней мере для одной из двух таблиц. Даже кодировка символов должна быть одинаковой для одинаковых столбцов строчного типа.

6. Не используйте ORDER BY RAND()

Это один их тех трюков, которые круто выглядят, и многие начинающие программисты попадают в его ловушку. Они даже представить не могут, какую ужасную проблему сами себе создают, начав использовать это выражение в своих запросах.

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

Так вы получаете случайное число, которое меньше, чем количество строк в результате запроса, и используете его как смещение в предложении LIMIT.

7. Старайтесь не использовать SELECT *

Чем больше данных будет прочитано из таблицы, тем медленнее выполняется запрос. Такие операции также занимают время для выполнения дисковых операций. А если сервер базы данных отделен от веб-сервера, то задержки будут вызваны еще и передачей данных по сети между серверами.

Хорошей привычкой является указание столбца при выполнении SELECT.

8. Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

Даже если в вашей таблице есть поле с уникальным именем пользователя, не делайте его основным ключом. Поля с типом VARCHAR медленно работают в качестве основных ключей. Также структура вашей базы данных будет лучше, если в ней внутри использовать ссылки на записи на основании id.

Кроме того механизм MySQL использует основные ключи для своих внутренних задач, и использование поля id создает оптимальные условия для их решения.

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

9. Используйте ENUM вместо VARCHAR

Столбцы типа ENUM очень компактные и быстрые. Они хранятся в базе данных как и TINYINT, но еще они могут содержать строчные значения. Такие особенности делают их отличными кандидатами для реализации определенных полей.

Если у вас есть поля, которые содержат только несколько различных видов значений, используйте для них ENUM вместо VARCHAR. Например, может быть столбец с именем “status”, который будет содержать только такие значения как “active”, “inactive”, “pending”, “expired” и так далее.

MySQL может “предложить” способ изменения структуры вашей таблицы. Когда вы создаете поле VARCHAR, то наверняка «предложение» будет содержать рекомендацию сменить тип столбца на ENUM. «Предложения» получаются в ходе выполнения вызова PROCEDURE ANALYSE().

10. Изучите предложения PROCEDURE ANALYSE()

PROCEDURE ANALYSE() позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать «предложения». Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Например, если вы создали поле типа INT для основного ключа, но в таблице не так много записей, то «предложение» может содержать рекомендацию сменить тип поля на MEDIUMINT. Или если вы используете поле типа VARCHAR, то можете получить «предложение» конвертировать его в ENUM, если в нем содержится только несколько значений.

Вы также можете получить рекомендации, если нажмете ссылку “Propose table structure” (Анализ структуры таблицы) в PhpMyAdmin на закладке структуры таблицы.

Нужно только помнить, что это всего лишь предложения. И если ваша таблица будет расти, то они могут оказаться неверными. Так что решение об их применении остается за вами.

11. Используйте NOT NULL, если это возможно

Если нет особых причин использовать значение NULL, нужно всегда использовать для столбца свойство NOT NULL.

Спросите себя, есть ли разница между пустой строкой и значением NULL (для полей типа INT: 0 и NULL). Если нет причин использовать оба значения, то нет необходимости иметь поле NULL. (Вы знаете, что Oracle рассматривает NULL и пустую строку как одинаковые величины?)

Столбец NULL требует дополнительного пространства и может быть источником затруднений для выражений сравнений. Просто избегайте использовать его по мере возможности. Хотя, конечно, есть объективные причины для использования значений NULL в некоторых случаях.

Из документации MySQL:

“Столбец NULL требует дополнительного пространства в строке для записи о возможном значении NULL. Для таблиц MyISAM каждый столбец NULL использует дополнительный бит, округление проводится до ближайшего байта.”

12. Подготовленные выражения

Есть несколько фактов преимущества использования подготовленных выражений в области безопасности и производительности.

Подготовленные выражения по умолчанию фильтруют переменные, которые к ним привязаны, что является очень хорошей защитой ваших приложений против атак типа «инъекция SQL». Конечно, вы можете фильтровать переменные вручную тоже, но такие методы более подвержены ошибкам и забывчивости программистов.

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

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

Было время, когда программисты избегали использования подготовленных выражений только по причине того, что они не кэшировались MySQL. Но начиная с версии 5.1 кэширование также поддерживается для запросов подготовленных выражений.

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

13. Небуферированные запросы

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

14. Храните IP адрес как UNSIGNED INT

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

В запросах можно использовать функцию INET_ATON() для конвертации IP адреса в целое, и INET_NTOA() для обратного процесса. Также есть схожие функции PHP: ip2long() и long2ip().

15. Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи”. Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

Таблицы «static» быстрее обрабатываются механизмом MySQL при поиске записей. Когда нужно прочитать определенную запись в таблице, то ее положение быстро вычисляется. Если размер строки не фиксирован, то для определения положения записи нужно время на поиск и сопоставление с индексом основного ключа.

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

Использование техники «Вертикальное разделение» дает возможность отделить столбцы с переменной длиной в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1: У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2: У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

17. Разделяйте большие запросы DELETE или INSERT

Если вам нужно выполнить большой запрос DELETE или INSERT на работающем сайте, то нужно быть осторожным, чтобы не нарушить трафик. Когда выполняется большой запрос, то он может заблокировать ваши таблицы и привести к остановке приложения.

Apache выполняет много параллельных процессов/потоков. по этой причине он работает более эффективно, когда скрипт заканчивает выполнение как можно быстрее, таким образом сервер не использует слишком много открытых соединений и процессов, потребляющих ресурсы, особенно память.

Если вы блокируете таблицы на продолжительное время (например, на 30 и более секунд) на высоко нагруженном веб сервере, вы можете вызвать накапливание процессов и запросов, что потребует значительного времени на расчистку или даже приведет к остановке вашего веб сервера.

Если у вас есть скрипт, который удаляет большое количество записей, просто используйте предложение LIMIT для разбиения его на маленькие партии, чтобы избежать описанной ситуации.

18. Маленькие столбцы обрабатываются быстрее

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

Если таблица будет содержать всего несколько строк, то нет причин делать основной ключ типа INT, а не MEDIUMINT, SMALLINT или даже TINYINT. если вам нужна только дата, используйте DATE вместо DATETIME.

Нужно только помнить о возможностях роста.

19. Выбирайте правильный механизм хранения данных

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

MyISAM отлично подходит для приложений с большой нагрузкой по чтению, но он не очень хорошо масштабируется при наличии большого количества записей. Даже если вы обновляете одно поле в одной строке, вся таблица будет заблокирована и ни один процесс не сможет ничего прочитать пока запрос не завершится. MyISAM быстро выполняет вычисления для запросов типа SELECT COUNT(*).

InnoDB является более сложным механизмом хранения данных, и он может быть более медленным, чем MyISAM для большинства маленьких приложений. Но он поддерживает блокирование строк, что лучше для масштабирования таблиц. Также он поддерживает некоторые дополнительные особенности, такие как транзакции.

20. Используйте объектно-реляционное отображение

Использование объектно-реляционного отображения (ORM — Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM , можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

ORM отлично подходит для «ленивой загрузки». Это означает, что получение значений возможно тогда, когда они нужны. Но нужно быть аккуратным, потому что можно создать много маленьких запросов, которые понизят производительность.

ORM может также объединять ваши запросы в транзакции, которые выполняются существенно быстрее, чем индивидуальные запросы к базе данных.

Для PHP можно использовать ORM Doctrine.

21. Будьте осторожны с постоянными соединениями

Постоянные соединения предназначены для сокращения потерь на восстановление соединений к MySQL. Когда создается постоянное соединение, то оно остается открытым даже после завершения скрипта. Так как Apache повторно использует дочерние процессы, то процесс выполняется для нового скрипта, и он использует тоже соединение с MySQL.

Это звучит здорово в теории. Но в действительности это функция не стоит медного гроша из-за проблем. Она может вызывать серьезные неприятности с ограничениями количества соединений, переполнение памяти и так далее.

Apache работает на принципах параллельности, и создает множество дочерних процессов. Вот в чем заключается причина того, что постоянные соединения не работают как ожидается в данной системе. Прежде, чем использовать функцию mysql_pconnect(), проконсультируйтесь с вашим системным администратором.

Данный урок подготовлен для вас командой сайта ruseller.com
Источник урока: net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/
Перевел: Сергей Фастунов
Урок создан: 27 Августа 2010
Просмотров: 193471
Правила перепечатки

5 последних уроков рубрики «Разное»

Как разместить свой сайт на хостинге? Правильно выбранный хороший хостинг — это будущее Ваших сайтов

Проект готов, Все проверено на локальном сервере OpenServer и можно переносить сайт на хостинг. Вот только какую компанию выбрать? Предлагаю рассмотреть хостинг fornex.com. Отличное место для твоего проекта с перспективами бурного роста.

Разработка веб-сайтов с помощью онлайн платформы Wrike

Создание вебсайта — процесс трудоёмкий, требующий слаженного взаимодействия между заказчиком и исполнителем, а также между всеми членами коллектива, вовлечёнными в проект. И в этом очень хорошее подспорье окажет онлайн платформа Wrike.

20 ресурсов для прототипирования

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

Топ 10 бесплатных хостингов

Небольшая подборка провайдеров бесплатного хостинга с подробным описанием.

Быстрая заметка: массовый UPDATE в MySQL

Ни для кого не секрет как в MySQL реализовать массовый INSERT, а вот с UPDATE-ом могут возникнуть сложности. Чтобы не прибегать к манипуляциям события ON_DUPLICATE можно воспользоваться специальной конструкцией CASE … WHEN … THEN.

Топ-пост этого месяца:  Google начал наказывать за неправильные иконки в поисковых сниппетах