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

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

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

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

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

См. также:
Как удалить бинарные логи сервера MySQL?
Типы таблиц MySQL
Не используйте SELECT * FROM

Если необходимо экспортировать БД MySQL большого объема, то не стоит пытаться сделать это через какой-нибудь PhpMyAdmin, все равно не получится :). На самом деле в этом случае есть два пути решения:

1) Попросту, остановив предварительно MySQL, скопировать файлы таблиц. Формат файлов для таблиц MyISAM одинаков для всех платформ.

2) Воспользоваться стандартной утилитой mysqldump, например, так:

mysqldump -uuser -ppassword database > database.sql

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

В базе данных можно хранить особо важный данные и в закрытом зашифрованном виде. Например, при помощи функций DES_ENCRYPT и DES_DESCRYPT. В этом случае данные шифруются при помощи алгоритма Triple-DES. Правда, для работы с этими функциями MySQL должен быть собранным с поддержкой SSL. Используются эти функции следующим образом:

DES_ENCRYPT('строка для шифрования', 'ключ')

DES_DECRYPT('зашифрованная строка', 'ключ')

См. также:
Шифрование данных в MySQL
VARCHAR, VARCHAR…
Упаковка/распаковка данных в таблицах

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

1) сначала установите требуемые модули Perl:

cpan -i Term::ReadKey
cpan -i Term::ANSIColor
cpan -i Time::HiRes

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

2) Далее установите сам mytop:

wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
tar -zxvf mytop-1.6.tar.gz
cd mytop-1.6
perl Makefile.PL
make
make test
make install

3) Для просмотра информации о базе данных test, введите:

mytop -u root -p 'password' -h localhost -d test

Здесь
-u : имя пользователя
-p : пароль
-h : хост
-d : имя базы данных.

См. также:
Создание новой таблицы на базе старой ч. 2
SHOW CONTRIBUTORS
Создание новой таблицы по образу и подобию уже существующей

Это, конечно, логично, но не всегда сразу очевидно. При работе с таблицей типа MEMORY важно всегда знать, что удаление строк в ней не влияет на размер занимаемой памяти, до тех пор, пока не будет выполнен хотя бы такой фейковый запрос (фейковый потому, что по сути ничего в структуре таблицы не меняется, хотя размер занимаемой ОЗУ при этом пересчитывается):

ALTER TABLE tbl_name engine=memory;

См. также:
Список поддерживаемых типов таблиц в MySQL
Типы таблиц MySQL
Имена таблиц чувствительные к регистру… но не всегда!

Забавно, но в линуксоподобных системах, как и ожидалось, таблицы META и meta это совершено разные таблицы. И это хорошо. Плохо если проект разрабатывался изначально в MAC OS X или Windows, поскольку там такого различия (впрочем как и для файлов) не делается. Будьте внимательны! Лучшее решение с учетом вышеописанного - называть таблицы в едином стиле, к примеру, всегда в нижнем или всегда в верхнем регистре.

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

Интересно, что начиная с версии MySQL 5.0.3 поля типа VARCHAR() могут быть длинной до 65535 байт. Ранее поля данного типа не могли быть длинее 255 символов.
Впрочем, не стоит забывать о том, что вообще максимальный размер всей строки в таблице MySQL не может быть более чем 65535 байт (суммарно по всем столбцам). Также следует помнить что в кодировки UTF-8 один символ может требовать для хранения до 3 байт данных.

Также для данного релиза MySQL примечательно то, что начиная с версии 5.0.3 концевые пробельный символы при вставке в поля VARCHAR не удаляются, как это было ранее. Будьте внимательны!

См. также:
Получение списка возможных значений поля ENUM
Дата последнего обновления таблицы
MySQL+XML: новые операторы в версии 5.1.5

Судя по документации бинарные логи MySQL можно легко удалить, выполнив под соответствующими привилегиями запрос вида:

RESET master;

См. также:
Как лучше всего удалить одинаковые строчки из таблицы?
Быстрый перенос базы данных с одного сервера на другой
Как узнать версию MySQL?

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

1. Войдите в консоль на исходном сервере.

2. Введите команду mysqladmin -h ‘destination_host’ -u user -p create dest_db, где destination_host - ip адрес или имя удаленного сервера, user - имя пользователя на удаленном сервере, а dest_db имя создаваемой базы данных.

3. Введите команду mysqldump -u user2 –password=XXXX –opt –compress source_db | mysql -h ‘destination_host’ -u user -p dest_db, user2 и XXXX это логин и пароль на локальном сервере.

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

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

SET max_allowed_packet = 1500000;

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

См. также:
LAST_INSERT_ID и его параметр
DES_ENCRYPT и DES_DECRYPT
Вывод результатов запроса по вертикали в консоли MySQL

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

• MyISAM
• InnoDB
• BerkeleyDB (BDB)
• MERGE

Наиболее важное свойство таблицы - поддержка ею транзакций или нет. Поддержку транзакций обеспечивают только таблицы InnoDB и BDB. Кроме того, только таблицы MyISAM поддерживают полнтекстовый поиск.

MyISAM

MyISAM является типом таблицы по умолчанию. Такие таблицы работают очень быстро, но без обеспечения механизма транзакций. Размер таких таблиц зависит от операционной системы, хоть и файлы таблиц можно смело копировать из одной операционной системы в другую. Максимальное число ключей в таблице - 64, максимальная длина ключа - 1024 байта.

InnoDB

Таблицы InnoDB table поддерживают транзакции и блокировку на уровне строки таблицы. Как и у таблиц MyISAM, файлы данных таблиц InnoDB можно легко копировать из одной операционной системы в другую. Главный недостаток таких таблиц - они требуют больше дискового пространства для хранения данных.

BDB

BDB схожи с InnoDB и также поддерживают транзакции.. Они поддерживают блокировки на уровне старницы, но файлы данных из разных систем у BDB не совместимы.

MERGE

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

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

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

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

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

Поскольку MySQL 6.0 уже имеет не один билд, а целый ряд, мне бы хотелось сделать обзор новых возможностей 6-й версии, по сравнению с 5-й. Вот что получилось:

• Новый транзакционный движок (тип) БД Falcon.
• Поддержка ряда новых кодировок - utf-16, utf-32 и 4-байтная utf-8
• Новые конструкции - BACKUP DATABASE и RESTORE, для создания резервных копий и, соответственно, восстановления данных из них.
• Расширения оптимизатора для быстрого выполнения подзапросов и объединения таблиц.
• Расширение функциональности по работе с XML, включая новую конструкцию LOAD XML
• Конструкция SIGNAL, предназначенная для передачи данных об ошибках клиентскому приложению.
• Ряд других мелочей.

См. также:
Как быстро узнать сколько всего строк без учета LIMIT?
6 важных советов по созданию БД в MySQL
Упаковка/распаковка данных в таблицах

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

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

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

Если вы планируете работать с MySQL в кодировке UTF-8 (обычно это нужно когда и сайт будет в соответствующей кодировке), то вам нужно знать несколько особенностей по работе с БД:

• После подключения к БД из скриптов первым обязательно выполните запрос SET NAMES UTF8
• У самой БД, всех ее таблицах и полях должна быть указана кодировка и сравнение utf8_general_ci

См. также:
Кодировки, кодировки, кодировки…
Пара слов о полнотекстовом поиске
DES_ENCRYPT и DES_DECRYPT

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

SELECT CRC32('Считаем контрольные суммы в MYSQL');

выведет 2586716546. Великолепно!

См. также:
О конструкции SELECT CASE
Имена таблиц чувствительные к регистру… но не всегда!
Медленный ORDER BY RAND()

Многие ли знают что MySQL, начиная с версии 4.1.1, поддерживает две замечательные функции - COMPRESS и DECOMPRESS? Служат они для сжатия объемных данных. Могут применятся при вставке и выборке данных:

INSERT INTO data VALUES(COMPRESS('ооочень длинная строка'))

SELECT DECOMPRESS(TEXT) FROM data

См. также:
Опция SQL_BUFFER_RESULTS
Быстрый перенос базы данных с одного сервера на другой
VARCHAR, VARCHAR…

Начиная с версии 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

В 6-й версии MySQL появился новый служебный запрос - SHOW CONTRIBUTORS, выводящий таблицу с информацией о людях, которые вносят вклад в развитие MySQL или осуществляют поддержку компании MySQL AB. Для каждого лица отображается имя, местоположение и комментарий.

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