Записки MySQL девелопера

Результаты того или иного запроса можно запросто сохранить прямо из запроса SELECT в тот или иной файл. Например:

SELECT * INTO OUTFILE '/home/me/out.txt' FROM MY_TABLE;

См. также:
Получение данных о стуктуре таблицы
Повозимся с NULL-полями таблиц!
Конструкция SELECT SQL_NO_CACHE…

Речь идет о функциях AES_ENCRYPT и AES_DECRYPT, позволяющих шифровать и расшифровывать данные с использованием официального алгоритма AES (”Расширенный стандарт шифрования”). Шифрование выполняется с длинной ключа в 128 бит. Впрочем, как написано в официальной документации MySQL, можно увеличить длину до 256 бит, повозившись с исходниками.

SELECT AES_DECRYPT(
    (SELECT AES_ENCRYPT('секретный текст', 'мой ключ')), 
    'мой ключ'
)

См. также:
DES_ENCRYPT и DES_DECRYPT
Повозимся с NULL-полями таблиц!
Быстрая справка по MySQL :)

Мало, наверное, кто знает что в эту функцию еще можно и параметр передавать. А знаете для чего? Значение параметра (или выражения) переданное в функцию будет ID, который вернет эта же функция в следующий раз. Это удобно, согласно документации MySQL для построения тех или иных числовых последовательностей.

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

См. также:
Повозимся с NULL-полями таблиц!
Дата последнего обновления таблицы
Изменяем параметр max_allowed_packet на лету

Начиная с версии 5.1.5 MySQL стал поддерживать работу с XML значениями полей или если быть точным поддерживать XPath-функции: ExtractValue(), извлечение значения из xml-строкм, UpdateXML() - замена фрагмента xml-кода в строке. Например:

SELECT ExtractValue('<a>ccc<b>ddd</b></a>', '/a');

SELECT UpdateXML('<a><b>ccc</b><d></d></a>', '/a', '<e>fff</e>');

См. также:
Что нового в MySQL 6?
Повозимся с NULL-полями таблиц!
“Спящие” запросы в MySQL

Опция SQL_SMALL_RESULT использовуется с конструкциями GROUP BY и DISTINCT в тех случаях когда необходимо указать MySQL что результат запроса будет содержать мало строк. В этом случае MySQL использует быстрые временные таблицы и не сортирует данные во временнных таблицах.

См. также:
SELECT SQL_BIG_RESULT…
Повозимся с NULL-полями таблиц!
Быстрая справка по MySQL :)

Опция SQL_BIG_RESULT использовуется с конструкциями GROUP BY и DISTINCT в тех случаях когда необходимо указать MySQL что результат запроса будет содержать много строк. В этом случае MySQL использует временные таблицы на диске и сортирует временные таблицы с ключми элементов из секции GROUP BY.

См. также:
SELECT SQL_SMALL_RESULT…
Быстрая справка по MySQL :)
Повозимся с NULL-полями таблиц!

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

ЗЫ. Купил недавно себе смартфон ASUS P750 в интернет магазине 1Good Екатеринбург. Сижу немогу нарадоваться :).

См. также:
Упаковка/распаковка данных в таблицах
Повозимся с NULL-полями таблиц!
6 важных советов по созданию БД в MySQL

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

SHOW CHARACTER SET

См. также:
Повозимся с NULL-полями таблиц!
Кодировки, кодировки, кодировки…
Конструкция SELECT SQL_NO_CACHE…

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

SHOW TABLES

См. также:
Вывод доступных баз данных MySQL
Название текущей базы данных MySQL
Список поддерживаемых типов таблиц в MySQL

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

См. также:
Быстрая справка по MySQL :)
Повозимся с NULL-полями таблиц!
Конструкция SELECT … WITH ROLLUP

Указание опции SQL_NO_CACHE в конструкции SELECTa запрещает кэшировать результат запроса MySQL-сервером. Полезно в системах, где данные изменяются очень быстро, а актуальность полученной информации поставлена во главу угла.

См. также:
Предотвращаем кэширование запросов
Быстрая справка по MySQL :)
Конструкция SELECT … WITH ROLLUP

Что такое образ в MySQL? По сути это виртуальная таблица, “содержащая” результаты того или иного запроса (возвращающая данные запроса). Фактически это очень похоже на сохраненный запрос к БД.

Для создания образа в MySQL можно воспользоваться следующей командой:

CREATE VIEW texts_view AS SELECT * FROM texts

Образы (view) доступны в MySQL, начиная с версии 5.0.1.

См. также:
Повозимся с NULL-полями таблиц!
Создание новой таблицы на базе старой ч. 2
Конструкция SELECT SQL_NO_CACHE…

Эх, все таки умные ребята разрабатывают MySQL. Очень часто, когда мы делаем запрос с группировкой, мы проводим суммирование (и другие операции, допустимые только для запросов SELECT…GROUP BY) по тому или иному полю. Так вот… оказывается можно легко по таким агрегированным полям подвести итоговую сумму. Для этого нужно вместо запроса вида:

SELECT SUM(payment) FROM sales GROUP BY id_good

написать:

SELECT SUM(payment) FROM sales GROUP BY id_good WITH ROLLUP

Последней строкой у нас выведется итоговая сумма по агрегированному полю SUM(payment). Единственное ограничение на подобного рода конструкции - запрет на использование ORDER BY.

См. также:
Быстрая справка по MySQL :)
Когда MySQL не будет использовать индексы?
Как избежать дубликатов в таблице при помощи индексов?

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

CREATE TABLE new_table SELECT * FROM existing_table

Фактически это конструкция для создания новой таблицы на основании какой-либо выборки, так например, можно построить частичную (по структуре) копию таблицы:

CREATE TABLE new_table SELECT a, b FROM existing_table

См. также:
Повозимся с NULL-полями таблиц!
SELECT SQL_BIG_RESULT…
Создание новой таблицы по образу и подобию уже существующей

Все очень просто, STRAIGHT JOIN это специальный оператор, заставляющий MySQL объединять (”джойнить”) таблицы в том порядке, в каком это указано в SQL-запросе. Зачастую данный оператор позволяет неплохо оптимизировать скорость выполнения запроса. Пользуйтесь! Всем рекомендую! Пример:

SELECT STRAIGHT JOIN * FROM 
table1 
INNER JOIN 
table2 
ON table1.field1 = table2.field2

См. также:
Выбор данных из нескольких таблиц MySQL: JOIN или не JOIN?
Быстрая справка по MySQL :)
Повозимся с NULL-полями таблиц!

На вопрос JOIN’ить или не JOIN’ить в запросе таблицы ответ зачастую простой, - не JOIN’ить, если требуется конструкция INNER JOIN вида:

SELECT * FROM 
table1 
INNER JOIN 
table2 
ON table1.field1 = table2.field2

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

SELECT * FROM table1, table2 
WHERE table1.field1 = table2.field2

См. также:
Для чего нужен STRAIGHT JOIN?
Быстрая справка по MySQL :)
Свой ORDER BY

Забавно, но на самом деле копию (по структуре) таблицу сделать можно очень легко, - простым и понятным SQL-запросом следующего вида:

CREATE TABLE NewTableName LIKE OldTableName

Недавно, коллега тут завис на пару минут когда понадобилась копия структуры таблицы :). Естественно, что в каком-нибудь EMS MySQL Manager копия делается в один клик, но когда у тебя только PhpMyAdmin, то стоит знать синтаксис подобных запросов.

См. также:
Создание новой таблицы на базе старой ч. 2
Когда MySQL не будет использовать индексы?
Повозимся с NULL-полями таблиц!

Название текущей базы данных MySQL можно получить самым что ни на есть тривиальным способом, если кто-то не знал:

SELECT DATABASE()

Странно, но обычно я знаю с какой базой работаю

См. также:
Вывод доступных таблиц текущей базы данных MySQL
Вывод доступных баз данных MySQL
Экспорт данных большого объема в БД на хостинге

Конечно же, всем известен запрос RENAME TABLE … А кто-нибудь знал что за 1 запрос можно переименовывать любое число таблиц? Я, если честно, не знал. Оказывается все очень просто:

RENAME TABLE 
old_table_name_1 TO new_table_name_1, 
old_table_name_2 TO new_table_name_2;

См. также:
Замена подстроки в заданном столбце таблицы
Повозимся с NULL-полями таблиц!
“Спящие” запросы в MySQL

Узнать список поддерживаемых типов таблиц в MySQL можно командой SHOW ENGINES:

mysql> SHOW engines\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: DEFAULT engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: HASH based, stored IN memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: INNODB
Support: YES
Comment: Supports transactions, row-level locking, AND FOREIGN KEYS
*************************** 4. row ***************************
 Engine: BERKELEYDB
Support: NO
Comment: Supports transactions AND page-level locking
*************************** 5. row ***************************
 Engine: BLACKHOLE
Support: NO
Comment: /dev/NULL storage engine (anything you WRITE to it disappears)
*************************** 6. row ***************************
 Engine: EXAMPLE
Support: NO
Comment: Example storage engine
*************************** 7. row ***************************
 Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
*************************** 8. row ***************************
 Engine: CSV
Support: YES
Comment: CSV storage engine
*************************** 9. row ***************************
 Engine: ndbcluster
Support: DISABLED
Comment: Clustered, fault-tolerant, memory-based tables
*************************** 10. row ***************************
 Engine: FEDERATED
Support: YES
Comment: Federated MySQL storage engine
*************************** 11. row ***************************
 Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 12. row ***************************
 Engine: ISAM
Support: NO
Comment: Obsolete storage engine
12 rows IN SET (0.00 sec)

Здесь же выводится краткое описание каждого из типов. У меня на сервере, как видите, поддерживается целых 12 типов таблиц

См. также:
Типы таблиц MySQL
Дата последнего обновления таблицы
Вывод доступных таблиц текущей базы данных MySQL

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

SELECT 
name, 
IF(description IS NULL, "Описание не найдено", description) 
FROM goods

в данном случае если у товара из таблицы goods нет описания (значения поля равно NULL), система подменит значение NULL на “Описание не найдено”.

См. также:
О конструкции SELECT CASE
GROUP BY & ORDER BY null
Получение данных о стуктуре таблицы

А вы знали, что начиная с версии 5.0.12 запросы в MySQL могут “спать”? Как? Да очень просто… так, например, запрос

SELECT sleep(5)

сделает паузу в исполнении на 5 секунд. При успешном завершении конструкции возвращается 0, при прерывании ее исполнения 1. Возможно указания отрезка времени в дробном представлении с точностью до микросекунд.

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT SQL_NO_CACHE…
Быстрая справка по MySQL :)

А вы знаете кто эти славные ребята? Знали, но забыли? Воспользуйтесь командой

SHOW AUTHORS

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT … WITH ROLLUP
Вывод доступных таблиц текущей базы данных MySQL

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

SHOW OPEN TABLES

Примечательно, что лично у меня открыты и те таблицы, какие я не использую уже более месяца

См. также:
Быстрая справка по MySQL :)
Повозимся с NULL-полями таблиц!
Список поддерживаемых типов таблиц в MySQL

А вы замечали, что LIKE обычно регистронезависим? А знаете как его отучить от этого дела? А очень просто, нужно всего лишь записывать этот оператор в виде:

SELECT * FROM texts WHERE name LIKE BINARY '%бла%'

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT SQL_NO_CACHE…
Для чего нужен STRAIGHT JOIN?

В MySQL существует конструкция вида SELECT CASE, реализующая оператор выбора. К примеру, запрос

SELECT CASE id 
   WHEN 1 THEN 'первый' 
   WHEN 2 THEN 'второй' 
   ........ 
END FROM table_name

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

См. также:
Повозимся с NULL-полями таблиц!
Быстрая справка по MySQL :)
Когда MySQL не будет использовать индексы?

Для того, чтобы гарантировано избежать дубликатов данных в таблице по нескольких полям достаточно задать объединенный уникальный индекс для этих полей. Т. е. если у нас есть, к примеру, таблица с городами и областями и каждое сочетание ГОРОД-ОБЛАСТЬ должно быть уникальным, то нужно просто создать уникальный индекс по полю ГОРОДА и полю ОБЛАСТИ. В этом случае добавление записи с уже существующей парой ГОРОД-ОБЛАСТЬ не будет выполнено MySQL.

См. также:
Когда MySQL не будет использовать индексы?
Конструкция SELECT … WITH ROLLUP
Как лучше всего удалить одинаковые строчки из таблицы?

Для получения данных о структуре заданной таблицы существует специальная конструкция вида

DESCRIBE table_name

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

См. также:
Повозимся с NULL-полями таблиц!
Статистика MySQL сервера
Дата последнего обновления таблицы

Просмотреть статистическую информацию о MySQL-сервере можно задав простую команду:

SHOW STATUS

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

См. также:
Повозимся с NULL-полями таблиц!
Когда MySQL не будет использовать индексы?
Конструкция SELECT SQL_NO_CACHE…

Да, да, запросы на выборку в MYSQL давно поддерживают регулярные выражения. Выполняется это при помощи оператора RLIKE. Например, так:

SELECT * FROM `texts` WHERE value RLIKE '.*dis.*'

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

См. также:
Повозимся с NULL-полями таблиц!
INSERT LOW_PRIORITY…
Что нового в MySQL 6?

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

SELECT SQL_NO_CACHE field_name FROM table_name

См. также:
Получение данных о стуктуре таблицы
Конструкция SELECT SQL_NO_CACHE…
О конструкции SELECT CASE

Число ситуаций, когда сервер MySQL не будет использовать индексы таблицы довольно ограничено. Итак, индексы не используются, когда:

• используются таблицы типа HEAP
• осуществляется поиск по одному индексированному полю, а сортировка по другому
• осуществляется поиск по полю при помощи конструкции LIKE с маской, начинающейся с символа %
• в запросе осуществляется поиск по частичному индексу

См. также:
Повозимся с NULL-полями таблиц!
Не используйте SELECT * FROM
Как избежать дубликатов в таблице при помощи индексов?

Оказывается конструкция ORDER BY RAND() работает достаточно медленно. В высоконагруженных базах данных рекомендуют вместо, к примеру:

SELECT id, name FROM positions ORDER BY RAND() LIMIT 5

писать запрос вида

SELECT id, name FROM positions WHERE id IN ('2', '14', '5', '3')

где набор id-шников генерируется случайно внешней программой (например, php-скриптом) в диапазоне от 1 до MAX(id).

См. также:
Свой ORDER BY
GROUP BY & ORDER BY null
Быстрая справка по MySQL :)

Собственно, не рекомендуется использовать запись вида "SELECT * FROM…" По ряду причин лучше использовать "SELECT id, name…. FROM":

• экономия памяти и, возможно, трафика между SQL и WWW сервером
• разработчик всегда будет знать какие поля выбираются из таблицы

См. также:
Повозимся с NULL-полями таблиц!
6 важных советов по созданию БД в MySQL
Конструкция SELECT SQL_NO_CACHE…

Сколько лет живи, столько и учись! На самом деле, я не знал о том, что в SQL-запросе значения любого столбца таблицы можно не только заменить на другие, но и преобразовать, заменив, например, одну подстроку на другую. Делается это очень легко и просто:

UPDATE table_name SET 
field_name = REPLACE(field_name, 'заменить_это', 'заменить_на_то');

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

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT SQL_NO_CACHE…
“Спящие” запросы в MySQL

Чтобы узнать значения переменных конфига MYSQL достаточно сделать запрос вида:

SHOW VARIABLES

См. также:
Как узнать версию MySQL?
Вывод доступных баз данных MySQL
Повозимся с NULL-полями таблиц!

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

Для решения этой проблемы советуют принудительно дописывать ORDER BY null в конце запроса. Я решил попробовать потестировать оба варианта запроса:

Обычный подход

mysql> EXPLAIN SELECT aff FROM dic GROUP BY aff\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dic
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7888
        Extra: USING temporary; USING filesort
1 row IN SET (0.00 sec)

Оптимизированный подход

mysql> EXPLAIN SELECT aff FROM dic GROUP BY aff ORDER BY NULL\G                 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: dic
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7888
        Extra: USING temporary
1 row IN SET (0.00 sec)

Как видно из результатов запрсоа EXPLAIN, на самом деле второй вариант будет отработан быстрее (я не про секунды в результатах), т.к. в по столбцу Extra видно что в первом варианте осуществляеся сортировка, а во втором - нет.

См. также:
Повозимся с NULL-полями таблиц!
Конструкция SELECT … WITH ROLLUP
6 важных советов по созданию БД в MySQL

Бывают ситуации, когда результаты запроса хотелось бы отсортировать в особом, своем порядке. Часто это случается с ENUM полями.

Совсем недавно узнал что для этого существует конструкция ORDER BY FIELD. Например, если у нас есть поле status типа ENUM с возможными значениями error, wait, process, ok, то мы можем отсортировать результаты запроса по нему в нужном нам порядке:

SELECT * FROM table_name 
ORDER BY FIELD (status, 'error', 'wait', 'process', 'ok')

См. также:
Медленный ORDER BY RAND()
Вывод результатов запроса по вертикали в консоли MySQL
GROUP BY & ORDER BY null

Сегодня коллега при написании универсального php-скрипта задал интересный вопрос - как получить список возможных значений поля ENUM. Оказывается список возможных значений для поля типа ENUM определить довольно-таки легко, - нужно сделать запрос вида

DESCRIBE table_name field_name

и распарсить полученный результат.

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

mysql> DESCRIBE positions pos_type\G
*************************** 1. row ***************************
  FIELD: pos_type
   Type: ENUM('simple','inner_block','out_block')
   NULL: NO
    Key:
DEFAULT:
  Extra:
1 row IN SET (0.00 sec)

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

P.S. Кстати, судя по некоторым сообщениям, лучше всего в значениях поля ENUM русские буквы не использовать.

P.S.2. Интересная статья о производительности полей типа ENUM.

См. также:
Свой ORDER BY
6 важных советов по созданию БД в MySQL
Повозимся с NULL-полями таблиц!

Иногда необходимо узнать число строк в таблице MySQL без учета оператора LIMIT. Большинство программистов в этой ситуации просто делают второй запрос - SELECT COUNT(*) FROM …, что, на самом деле, не очень хорошо. Обычно такая ситуация возникает при реализации постраничной прокрутки каких-либо данных.

На самом деле, начиная с 4-й версии MySQL существует возможность быстрого определения числа строк в запросе без учета ограничения по оператору LIMIT:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM positions LIMIT 1\G
*************************** 1. row ***************************
          id: 90
   id_series: 40
        name: MSC-GA25VB/MUH-GA25VB
      params: a:4:{i:2;s:3:"2,6";i:11;s:3:"3,0";i:4;s:11:"815x278x244";i:14;s:4:"0,82";}
   functions: a:0:{}
 description:
spec_comment:
    warranty: 3 ????
       price: 696.817
       FIXED: -1
         new: 0
    invertor: 0
        spec: 0
    pos_type: simple
     id_sort: 60
     visible: 1
1 row IN SET (0.00 sec)
 
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|          399 |
+--------------+
1 row IN SET (0.00 sec)

См. также:
Быстрая справка по MySQL :)
Кодировки, кодировки, кодировки…
Как сбросить пароль MySQL?

Никогда не задумывались как собственно SQL-запросом узнать версию сервера MySQL? На самом деле очень просто:

mysql> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.0.32-Debian_7etch6 |
+----------------------+
1 row IN SET (0.00 sec)

См. также:
Повозимся с NULL-полями таблиц!
Как узнать конфиг MySQL?
Конструкция SELECT SQL_NO_CACHE…

В языке запросов MySQL есть конструкция, позволяющая определить дату последнего обновления таблицы. Для чего это может быть полезно? Мне, например, понадобилось при построении системы “умного кеширования”. Также это может быть востребованным при том или ином виде сбора данных из сети с последующим сохранением данных в заданной таблице.

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

 mysql> SHOW TABLE STATUS LIKE 'positions'\G;
*************************** 1. row ***************************
           Name: positions
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 395
 Avg_row_length: 203
    Data_length: 80512
Max_data_length: 281474976710655
   Index_length: 9216
      Data_free: 0
 AUTO_INCREMENT: 454
    Create_time: 2009-01-19 16:29:27
    Update_time: 2009-01-20 11:12:12
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row IN SET (0.00 sec)

P.S. Люди еще умудряются этим запросом LAST_INSERT_ID брать, вместо того, чтобы написать SELECT LAST_INSERT_ID() :).

См. также:
Повозимся с NULL-полями таблиц!
SELECT HIGH_PRIORITY…
VARCHAR, VARCHAR…