MySQL. Библиотека профессионала - Аткинсон Л

Глава Инструкции SQL

В MySQL версии 4.0 должна появиться отдельная утилита, выполняющая описан ные выше действия.

BEGIN [WORK]

Эта инструкция начинает новую транзакцию. О рассказывалось в гла ве 9, "Транзакции и параллельные вычисления".

CHANGEMASTER

Эта инструкция изменяет параметры взаимодействия с главным сервером:

MASTER TO опция,

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

Опция	Описание
	Период ожидания (в секундах) перед повторной
	пыткой установить соединение
	Доменное имя или IP адрес главного сервера
g_f	Имя журнального файла на главном сервере
	Начальная позиция в журнальном файле
master_password	Пароль для регистрации на главном сервере
master_port	Порт для подключения к главному
master_user	Имя пользователя для регистрации на главном сервере

Инструкция CHANGE MASTER приводит лишь к временной смене главного сервера. Когда подчиненный сервер перезапускается, он руководствуется значениями, содер жащимися в файле конфигурации. Если необходимо сменить главный сервер на по стоянной основе, нужно остановить подчиненный сервер и внести изменения в его файл конфигурации (листинг 13.17).

change master to

master

Полный списокинструкций 213

CHECK TABLE

Инструкция CHECK TABLE проверяет таблицу на предмет наличия ошибок. Ее синтаксистаков:

CHECK TABLE	имя
	[EXTENDED] [FAST] [MEDIUM]	[QUICK]
Эта инструкция выполняет те же действия, что и команда			Разре
шается проверять только таблицы типа		Описание утилиты myisamchk при
ведено в главе 14, "Утилиты командной строки".

Можно указывать любое число таблиц и произвольные комбинации опций, хотя некоторые из них лишены смысла. Опция CHANGED задает проверку только тех таб лиц, которые изменились с момента последней проверки или же были неправильно закрыты. Проверка FAST выполняется над таблицами, которые помечены как непра вильно закрытые. Обе эти проверкипредназначены для запуска в пакетном режиме, возможноспомощьюутилиты планировщика.

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

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

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

В результате проверки выдается как минимум одна строка, но их может быть и не сколько. Если нет ошибок, последняя строка будет содержать сообщение ОК. В про тивном случае таблица может оказаться недоступной для работы, пока не будет ис правлена с помощью инструкции REPAIR TABLE.

COMMIT

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

214 Глава 13. SQL

CREATE DATABASE

Инструкция CREATE DATABASE создает базу данных (листинг 13.19). Синтаксис инструкции таков:

CREATE DATABASE [IF NOT EXISTS] имя

Если база данных с таким именем существует, а спецификатор IF NOT EXISTS не указан, будет выдано сообщение об ошибке. В MySQL каждая база данных хранится в отдельном подкаталоге, поэтому инструкция создаст пустой каталог. Просмотреть список существующих баз данных можно с помощью инструкции SHOW DATABASE.

CREATE DATABASE IF NOT EXISTS

CREATE FUNCTION

Инструкция CREATE FUNCTION загружает код функции, хранящийся в совместно используемой объектной библиотеке. Эта функция работает так же, как и любая встроенная функция. Синтаксис инструкции таков:

CREATE [AGGREGATE] FUNCTION имя

RETURNS

SONAME

Флаг AGGREGATE разрешает использовать функцию в предложении GROUP BY. Тип возвращаемого значения может быть STRING, REAL или INTEGER. Последний аргу мент инструкции — это путевое имя библиотеки.

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

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

CREATE INDEX

Инструкция CREATE	INDEX добавляет индекс к заданной таблице:
CREATE [UNIQUE |	INDEX имя
ON таблица

То же самое можно сделать с помощью инструкции ALTER TABLE.

CREATE TABLE

Инструкция CREATE TABLE предназначена для создания таблиц. Это, наверное, одна из наиболее сложных SQL инструкций. Общий ее формат таков:

Полный список инструкций 215

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя

.

I REPLACE]

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

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

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

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

имя тип

[NOT NULL | NULL]

значение]

[AUTO_INCREMENT] KEY]

Типы столбцов рассматривались в главе "Типы столбцов и индексов". Специ фикация типа включает название типа и его размерность.

По умолчанию столбцы принимают значения NULL. Спецификатор NOT NULL за прещает подобное поведение.

У любого столбца есть значение по умолчанию. Если оно не указано, программа MySQL выберет его самостоятельно. Для столбцов, принимающих значения NULL, зна чением по умолчанию будет NULL, для строковых столбцов — пустая строка, для численных столбцов — нуль. Изменить эту установку позволяет предложение DEFAULT.

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

В листинге 13.20 показан пример создания таблицы.

CREATE TABLE IF NOT EXISTS player

Столбцы
ID	(11) UNSIGNED		NOT NULL
Nickname		NOT	NULL,
Password		NOT NULL,
Rank		NOT	NULL	DEFAULT 50.0,
Region				NOT NULL,
Team	TINYINT(3)	UNSIGNED		NOT NULL,

Глава Инструкции SQL

Индексы

PRIMARY KEY

INDEX (Region,

FOREIGN KEY (Team) REFERENCES team (ID)

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

Чтобы активизировать работу необходимо вставить в таблицу строку, в которой соответствующее поле равно 0 или NULL. Функция LAST_INSERT_ID опи санная в главе 12, "Встроенные функции", позволяет узнать последнее значение, сге нерированное счетчиком.

Спецификация PRIMARY KEY позволяет назначить столбец первичным ключом. При этом для столбца будет создан индекс.

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

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

CHECK (выражение)

Эта спецификация поддерживается для совместимости с другими СУБД, но не не сет никакой смысловой нагрузки в MySQL.

[CONSTRAINT имя] FOREIGN имя (столбец, [ссылка]

Эта спецификация тоже не играет никакой роли в MySQL, существуя лишь в целях внешней совместимости. Имеет смысл включать ее в инструкцию CREATE TABLE в ка честве "документации" к схеме базы данных. В предложение FOREIGN KEY помечает группу столбцов как зависящую от набора столбцов другой таблицы, форми руя связь между но в MySQL правильность этой связи не контролируется.

Спецификация ссылки имеет следующий вид:

REFERENCES таблица	[MATCH FULL | MATCH PARTIAL]
[ON DELETE правило]	[ON UPDATE правило]

Синтаксис правил удаления и обновления таков:

| CASCADE | SET NULL | NO ACTION I SET

Полный список инструкций

[INDEX] [имя] (столбец

Эта спецификация задет полнотекстовый индекс для указанных столбцов.

| [имя] (столбец

Эта спецификация задет индекс для указанных столбцов.

PRIMARYKEY(столбец

Эта спецификация делает из одного или нескольких столбцов первичным ключом. Первичный ключ не может содержать дублирующиеся значения и значения NULL. У каждой таблицы есть не более чем один первичный ключ.

UNIQUE[INDEX][имя](столбец

Эта спецификация накладывает на группу столбцов ограничение уникальности.

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

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

CREATE TABLE team

Столбцы

ID TINYINT(3) UNSIGNED NOT NULL

Name NOT NULL,

Индексы

PRIMARY KEY

TYPE=MYISAM

О

ROW MAX

TYPE тип

Опция TYPE задает формат хранения таблицы. По умолчанию таблицы имеют тип Для таких таблиц поддерживается большинство функциональных возмож ностей, за исключением транзакций. Список всех возможных типов представлен в табл. 13.2. Типы HEAP, ISAM, MERGE и MylSAM доступны всегда. Поддержка осталь ных типов включается на этапе компиляции. Характеристики каждого типа рассмат риваются в главе 24, "Физическое хранение данных".

Глава Инструкции SQL

Тип	Описание
	Таблицы этого типа поддерживают транзакции благодаря библио
	теке функций Berkeley DB
Berkeley_db	Синоним типа BDB
HEAP	Таблицы этого типа хранятся в памяти
	Таблицы этого типа поддерживают транзакции благодаря
	теке функций
SAM	Этот формат использовался старыми версиями MySQL
MERGE	Это коллекция таблиц MylSAM, интерпретируемых как одно целое
SAM	Это стандартный тип таблиц

ьное_значение

Эта опция задает начальное значение поля счетчика. Она доступналишь для таб лиц типа MylSAM.

AVG_ROW_LENGTH длина

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

CHECKSUM |

Если эта опция включена, программа MySQL будет хранить контрольную сумму каждой записи. Это помогает осуществлять контроль ошибок с помощью инструкции CHECK TABLE. По умолчанию опция отключена. Кроме того, она доступна только для таблиц типа MylSAM.

COMMENT комментарий

С помощью этой опции таблицу можно снабдить комментарием. Длина коммента рия не должна превышать 60 символов, но в MySQL версии 4.0 предел возрастет до 255 символов.

MAX_ROWS число_строк

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

Полный список инструкций

MIN_ROWS число_строк

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

PACK_KEYS |

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

PASSWORD пароль

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

|

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

ROW_FORMAT = DEFAULT | DYNAMIC | STATIC | COMPRESSED

Эта опция задает формат хранения записей и на момент написания книги еще не реализована.

| STRIPED |

RAID_CHUNKS

RAID_CHUNKSIZE размер

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

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

(таблица,

Опция UNION задает слияние перечисленных таблиц (листинг 13.22).

220 Глава Инструкции SQL

CREATE TABLE

ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Item INT,

Price

CREATE TABLE

ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

Item INT,

Price

CREATE TABLE

ID INT NOT NULL,

Item INT,

Price

TYPE=MERGE

Запрос на выборку

В конце инструкции CREATE TABLE находиться инструкция SELECT. Ре зультаты запроса на выборку будут занесены в создаваемую таблицу. Если в самой струкции CREATE TABLE отсутствуют спецификации столбцов, то вид создаваемой таблицы будет соответствовать таблице результатов запроса. В противном случае столбцы результатов запроса будут добавлены к определенным ранее столбцам.

Флаги IGNORE и REPLACE определяют порядок обработки дублирующихся запи сей, извлекаемых инструкцией SELECT.

Незаметные изменения

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

Например, столбцы типа TIMESTAMP должны иметь размерность 6, 8, 12 или 14 символов, поэтому нечетное значение размерности в интервале от 7 до 13 будет пре образовано в ближайшее большее четное число. Кроме того, флаги NULL и NOT NULL для таких столбцов игнорируются, а значения NULL преобразуются в текущее время.

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

В главе 11, "Типы столбцов и индексов", говорилось о том, что у некоторых типов столбцов есть синонимы. Все эти синонимы будут автоматически преобразованы в эквивалентныеимтипы.

С помощью инструкции SHOW TABLES можно получить список таблиц базы дан ных, а с помощью инструкции DESCRIBE — просмотреть информацию о заданной таблице.

список инструкций

DELETE

Инструкция DELETE удаляет записи из таблицы. Ее синтаксис таков:

DELETE [LOW_PRIORITY] FROM таблица [WHERE условия]

[LIMIT

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

С помощью предложения LIMIT можно ограничить число удаляемых записей. Это позволяет разбивать длинные операции удаления на ряд более мелких операций, ко торые проще контролировать. Например, в листинге 13.23 удаляется по 10 записей за раз, ведь всего могут существовать сотни пользователей, которые не регистрирова лись в системе за прошедший месяц, к тому операция сравнения дат тоже занима ет определенное время.

DELETE	FROM user
WHERE	INTERVAL 30 DAY)
LIMIT	10

Если предложения WHERE и LIMIT отсутствуют, будут удалены все записи таблицы. То же самое делает инструкция TRUNCATE, но она выполняется гораздо быстрее. При удалении записей подобным образом программа MySQL не сообщитоб их числе. Если эта информация важна, задайте предложение WHERE, условие отбора которого всегда истинно.

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

DESCRIBE

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

I таблица [столбец I

Для ленивых: вместо слова DESCRIBE можно указывать его сокращенную форму DESC.

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

Допускается указывать конкретный столбец или шаблон имени. Шаблон заключается в кавычки и имеет такой же вид, как и в операторе LIKE (см. главу 10, "Типы данных, менные и выражения"). Аналогичные результаты выдает инструкция SHOW COLUMNS.