Листинг 4.10. Создание таблицы tbi

В круглых скобках через запятую указываются имена столбцов и их тип. Так, созданная таблица tbl имеет два поля: number, куда можно помещать целочисленные значения (int), и поле name, в котором размещаются строки (text). Максимальная длина имен таблиц и столбцов составляет 64 знака и может включать литеры, цифры и сим-сты '_' и '$'. Имя может начинаться с цифры, но не должно полностью состоять из цифр.

Проконтролировать вновь созданную таблицу можно при помощи оператора show BBS, который возвращает список таблиц текущей базы данных.

Листинг 4.11. Использование оператораshow tables

Для того чтобы просмотреть содержимое другой базы данных, следует предварительно выбрать ее с использованием оператора use (листинг 4.12).

Как видно из листинга 4.12, учебная база данных содержит четыре таблицы: catalogs, orders, products и users.

Удаление таблицы производится при помощи оператора drop table (листинг 4.13).

Наряду с обращением к таблице по ее имени, например tbl, существует расширенное обращение, включающее в себя имя базы данных, например, test.tbl.

Для того чтобы получить описание столбцов базы данных, можно воспользоваться оператором describe.Как видно из листинга 4.14, имя таблицы приведено в расширенном формате test.tbl, однако вполне допустимо указание одного имени таблицы tbl, если в качестве текущей базы данных выбрана test.

Оператор describe возвращает таблицу, каждая строка которой соответствует столбцу таблицы tbl. Имена столбцов и их типы приводятся в первом и втором столбцах соответственно. К остальным столбам результирующей таблицы мы вернемся по мере освоения материала.

Типы таблиц

- MyISAM. Таблицы этого типа работают очень быстро и поддерживают полнотекстовую индексацию.

- ISAM является старым типом таблиц. Подобен MyISAM, но с меньшими возможностями.

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

- BDB является механизмом хранения, который обеспечивает поддержку транзакций и блокировки на уровне страниц.

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

- Таблицы MERGE позволяют объединить несколько таблиц MylSAM с одной структурой, чтобы к ним можно было направлять запросы как к одной таблице. Это можно использовать для того, чтобы обойти ограничения операционной системы на максимальный размер файлов, а следовательно, и таблиц.

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

- AUTO_INCREMENT= # Позволяет установить начальное значение auto_ increment, отличное от 1.

- AVG_ROW__LENGTH = # Позволяет указать оценку для средней длины строки, чтобы помочь механизму хранения.

- CHECKSUM = 1 Позволяет включить вычисление контрольной суммы для строк таблицы, что может помочь обнаружить проблему при повреждении таблицы. Чтобы включить вычисление контрольной суммы, укажите значение 1. По умолчанию эта опция выключена и работает только с таблицами MylSAM.

- COMMENT = " строка" Сохраняет комментарий для этой таблицы.

- MAX_ROWS= # Задает максимальное число строк, которые будут храниться в таблице.

- MIN_ROWS= # Задает минимальное число строк, которые будут храниться в таблице.

- PACK_KEYS = {0 | 1| DEFAULT}По умолчанию MySQL упаковывает (сжимает) строки в ключах. Если указать значение 1, все ключи будут упакованы, а если указать 0, никакие ключи упаковываться не будут.

- PASSWORD= "строка"В стандартной версии MySQL не выполняет ничего.

- DELAY_KEY_WRITE= {0 |1}Позволяет отложить обновление ключей до того, как таблица будет закрыта. Работает только с таблицами MylSAM.

- ROWJFORMAT = {default | dynamic | fixed | compressed} - позволяет указать формат хранения для строк. Работает только с таблицами MylSAM.

- RAID_TYPE = {1 | STRIPED | RAIDO} RAID_CHUNKS=# RAID_ CHUNKS I ZE=#Позволяет указать конфигурацию RAID с целью оптимизации.

- UNION = (имя_таблицы, [имя_таблицы.. . ]) Касается только таблиц MERGE и позволяет указать таблицы, которые должны быть частью MERGE.

- INSERT_METHOD = {NO | FIRST | LAST}Касается только таблиц MERGE и позволяет указать таблицу, в которую должны добавляться данные.

- DATA DIRECTORY-полный путь к каталогу"Позволяет указать место, где должны храниться данные таблицы.

- INDEX DIRECTORY-полный путь к каталогу"Позволяет указать место, где должны храниться индексы таблицы.

Типы данных

MySQL поддерживает несколько типов данных.

- Числовые данные — к ним относят целые числа, не содержащие дробной части (например, 124), а также вещественные числа, состоящие из последовательности цифр, разделенных точкой (например, 56.45).

- Строковые данные — последовательность символов, заключенных в одинарные или двойные кавычки: 'Hello world', '123', "MySQL". В качестве стандарта в SQL определяются одинарные кавычки, поэтому для совместимости с другими базами данных рекомендуется использовать именно их.

- Календарные данные — специальный тип для обозначения даты и времени, может принимать различную форму, например строковую "2005-04-28" или числовую 20050428. Основной характеристикой этого типа данных является их хранение в едином внутреннем формате, позволяющем осуществлять операции сложения и вычитания, независимо от внешнего представления.

- NULL — специальный тип данных, обозначающий отсутствие информации.

Числовые данные

Числовые данные делятся на точечные (BOOLEAN, INTEGER и DECIMAL) и приближенные (FLOAT, REAL и DOUBLE PRECISION). Характеристики и занимаемый объем точечных типов приведены в табл.1.

Таблица 1 Точечные типы

Как видно из табл. 1, СУБД MySQL имеет пять целых типов: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Различие между ними заключается в диапазоне величин, которые можно хранить в столбцах такого типа. Чем больше диапазон значений у типа данных, тем больше памяти для него требуется.

Целые типы данных могут быть объявлены положительными. Для этого после объявления типа следует использовать ключевое слово UNSIGNED. В этом случае элементам данного столбца нельзя будет присвоить отрицательные значения, а допустимый диапазон, который может принимать тип, удваивается. Так, тип TINYINT может принимать значения от -128 до 127, a TINYINT UNSIGNED — от 0 до 255.

При объявлении целого типа задается количество отводимых под число символов М (от 1 до 255). Это необязательное указание количества выводимых символов используется для дополнения пробелами слева от выводимых значений символов, меньших, чем заданная ширина столбца. Однако ограничений ни на диапазон величин, ни на количество разрядов не налагается. Если число символов, необходимых для вывода числа, превышает М, под столбец будет выделено больше символов. Если дополнительно указан необязательный атрибут ZEROFILL, свободные позиции по умолчанию дополняются нулями слева. Например, для столбца, объявленного как INT (5) ZEROFILL, величина 4 отображается как 00004.

Тип BIT [ (М) ] предназначен для хранения битовых полей. Параметр М указывает число битовых значений, которое может принимать поле (от 1 до 64). Если параметр М не указан, то по умолчанию принимает значение 1.

Замечание

Тип bit добавлен в MySQL, начиная с версии 5.0.3.

Тип BOOLEAN является синонимом для TINYINT (1). Значение 1 рассматривается как истина (true), a 0 как ложь (false).

Тип DECIMAL, а также его синонимы NUMERIC и DEC предназначены для величин повышенной точности, например для денежных данных. Требуемая точность задается при объявлении столбца данных одного из этих типов, например:

salary DECIMAL(5,2)

В этом примере цифра 5 определяет общее число символов, отводимых под число, а цифра 2 задает количество знаков после запятой. Следовательно, в этом случае интервал величин, которые могут храниться в столбце salary, составляет от -99,99 до 9.99 (в действительности для данного столбца MySQL обеспечивает возможность хранения чисел вплоть до 999.99, поскольку допускается не хранить знак для положительных чисел).

Величины типов DECIMAL, DEC и NUMERIC хранятся как строки, а не как двоичные числа с плавающей точкой, чтобы сохранить точность представления этих величин в десятичном виде. Если второй параметр равен 0, то величины DECIMAL и NUMERIC не содержат десятичного знака или дробной части.

Для представления вещественных типов в СУБД MySQL имеется три типа: FLOAT, DOUBLE И DECIMAL.

Замечание

Тип DOUBLE имеет два синонима: PRECISION и REAL. Тип DECIMAL имеет синоним NUMERIC

Характеристики приближенных типов представлены в табл. 2.

Таблица 2. Приближенные типы

Диапазон вещественных чисел помимо максимального значения имеет также минимальное значение, которое характеризует точность данного типа. Параметр М в табл. 2 задает число символов для отображения всего числа, a D— для его дробной части.

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

При выборе столбцов для формирования структуры таблицы необходимо обращать внимание на размер, занимаемый тем или иным типом данных: если значения, размещаемые в базе данных, никогда не будут выходить за пределы 100, не следует выбирать тип больше TINYINT. Если же в полях столбца предполагается хранить только целочисленные данные, то применение атрибута UNSIGNED позволит увеличить диапазон в два раза.

Строковые данные

Для строковых типов данных максимальный размер и требования к памяти приведены в табл. 3. Здесь L— это длина хранимой в ячейке строки, а байты, приплюсованные к L, — накладные расходы для хранения длины строки.

Таблица 3. Строковые типы данных

Тип CHAR позволяет хранить строку фиксированной длины М, его дополняет тип VARCHAR, позволяющий хранить переменные строки длиной L. Значение М может принимать значения от 0 до 65535.

Замечание

До версии MySQL 5.0.3 значение м для типов char и varchar могло принимать значения только от 0 до 255.

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

Таблица 4. Сравнение типов CHAR и VARCHAR

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

Замечание

Начиная с версии 4,1.2, типы CHAR и VARCHAR рассматривают строки как последовательности символов. Это означает, что при использовании многобайтных кодировок, например UNICODE, размер строки в байтах будет больше, чем в символах. Для совместимости со старыми версиями в MySQL введены два специальных типа: BINARY и VARBINARY, которые эквивалентны типам CHAR и VARCHAR, но строка в них рассматривается как последовательность байтов, а не символов. К BiNARY-строкам не применимы кодировки и сортируются они как обычные последовательности байтов.

Типы BLOB и TEXT в СУБД MySQL во всем аналогичны и отличаются только в деталях. Например, при выполнении операций над столбцами типа text учитывается кодировка, а типа BLOB — нет.

Тип TEXT обычно используется для хранения больших объемов текста, в то время как BLOB — для больших двоичных объектов, таких как электронные документы, изображения, звуки и т. д.

К особым типам данных относятся ENUM и SET. Строки этих типов принимают значения из заранее заданного списка допустимых значений. Основное различие между ними заключается в том, что значение типа ENUM должно содержать точно одно значение из указанного множества, тогда как столбцы SET могут содержать любой или все элементы заранее заданного множества одновременно. Так, значения для столбца, объявленного как ENUM('у','n'), могут принимать только два значения: либо 'у', либо 'n'.

Для типа SET, так же как и для типа ENUM, при объявлении задается список возможных значений, но ячейка может принимать любое значение из списка, а пустая строка означает, что ни один из элементов списка не выбран. Например, значения для столбца SET(‘y’, ‘n’) могут принимать значения ('у','n'), (‘у'), ('n') и пустое множество ( ).

Типы ENUM и SET можно назвать строковыми лишь отчасти, т. к. при объявлении они задаются списком строк, но во внутреннем представлении базы данных элементы множеств сохраняются в виде чисел. Элементы типа ENUM нумеруются последовательно, начиная с 1. В зависимости от числа элементов в списке под столбец может отводиться 1 байт (до 256 элементов в списке) или 2 байта (от 257 до 65536 элементов в списке).

Элементы множества SET обрабатываются как биты, размер типа при этом также определяется числом элементов в списке: 1 байт (от 1 до 8 элементов), 2 байта (от 9 до 16 элементов), 3 байта (от 17 до 24 элементов), 4 байта (от 25 до 32 элементов) и 8 байт (от 33 до 64 элементов).

Календарные данные

СУБД MySQL имеет 5 видов столбцов для хранения календарных типов данных: DATE, DATETIME, TIME, TIMESTAMP и YEAR (табл. 5). Тип DATE предназначен для хранения даты, TIME для времени суток, a TIMESTAMP для представления и даты, и времени суток. Тип TIMESTAMP предназначен для представления даты и времени суток в виде числа секунд, прошедших с полуночи 1 января 1970 года. Тип данных year позволяет хранить только год.

Таблица 5. Календарные типы данных

Для значений, имеющих тип DATE и DATETIME, в качестве первой цифры ожидается год либо в формате "YYYY", например '2005-10-15', либо в формате "YY", например

'05-10-15'. После года через дефис указывается месяц в формате "ММ" (10), а затем день в формате "DD" (15).

В типах TIME и DATETIME время приводится в привычном формате hh:mm:ss, где hh — часы, mm — минуты, a ss — секунды. Дни, месяцы, часы, минуты и секунды можно записывать как с ведущим нулем: 01, так и без него: 1.-Например, все следующие записи идентичны:

'2005-04-06 02:04:08'

'2005-4-06 02:04:08'

'2005-4-6 02:04:08'

'2005-4-6 2:04:08'

'2005-4-6 2:4:08'

'2005-4-6 2:4:8'

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

'05-12-31 11:30:45'

'05.12.31 11+30+45'

'05/12/31 11*30*45'

'05@12@31 11^30^45'

Дата и время суток могут также быть представлены в форматах 'YYYYMMDDhhmmss' и YYMMDDhhmmss'. Например, строки '2005091528' и '050523091528' аналогичны '2005-05-23 09:15:28', однако строка '051122129015' уже не может рассматриваться как дата и время суток, т. к. значение для минут равно 90 и выходит за допустимый интервал. Вместо строк допустимы и целочисленные значения, например, 2005091528 и 0523091528 рассматриваются как '2005-05-23 09:15:28'.

Начиная с версии MySQL 4.1.1, при указании времени суток после секунд через точку можно также указать микросекунды, т.е. использовать расширенный формат вида 'hh:mm:ss.ffffrr’, например '10:25:14.000001'. Кроме расширенного формата, можно использовать краткие форматы 'НН:ММ' и 'НН' — вместо пропущенных величин будут подставлены нулевые значения.

Если время задается в недопустимом формате, то в поле записывается нулевое значение. Нулевое значение присваивается полям временного типа по умолчанию, когда им не присваивается инициирующее значение (табл. 6).

Формат типа timestamp совпадает с DATETIME, но во внутреннем представлении дата хранится в виде секунд, прошедших с полуночи 1 января 1970 года.

Если в таблице несколько столбцов TIMESTAMP, при модификации записи текущее время будет записываться только в один из столбцов (по умолчанию первый). Можно также указать явно столбец, которому необходимо назначать текущую дату при создании новой записи или изменении старой. Для того чтобы поля столбца принимали текущую дату при создании новой записи, следует после определения столбца добавить запись DEFAULT CURRENT_TIMESTAMP. Если требуется, чтобы текущее время выставлялось при модификации уже существующей записи, при использовании оператора update следует добавить конструкцию on update CURRENT_TIMESTAMP.

Тип данных NULL