====== MySQL шпаргалки ======

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

===== Общие факты =====

  * Полезно под каждую базу на сервере создавать своего пользователя
  * Кодировка базы может быть любой, если она UTF-8
  * В большинстве случаев лучше использовать движок **InnoDB**
  * В php лучше забыть про сильно устаревшее расширение **mysql** и по-возможности использовать **pdo** или **mysqli**
  * Новую копию **MySQL** всегда можно настроить и оптимизировать
  * Без особой нужды не стоит открывать **MySQL** наружу. Вместо этого можно сделать проброс портов

|<code bash>ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

</code>|

===== Работа с данными =====

==== Числа ====

  * На 32-битных системах практически нет смысла ставить для типа ''INTEGER'' свойство ''UNSIGNED'', так как такие большие числа в php не поддерживаются.  На 64-битных системах, php поддерживает большие числа, вплоть до ''MySQL BIGINT'' со знаком.
  * Связанные таблицы (**«Foreign keys»**) должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано **«INTEGER UNSIGNED DEFAULT 0 NOT NULL»** то и на другой должно быть указано аналогично
  * Для хранения булевых значений, нужно использовать ''TINYINT(1)''
  * А деньги лучше хранить в ''DECIMAL(10, 2)'', где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что ''DECIMAL(10,2)'' может сохранить 9999999,99

==== Строки ====

  * В старых версиях (до 5.0.3) ''VARCHAR'' была ограничена 255 символами, но сейчас можно указывать до 65535 символов
  * Помним, что тип ''TEXT'' ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь **«LONGTEXT»**
  * Самая правильная кодировка для нашей БД UTF-8

==== Даты ====

Не забываем, что:

  * ''DATE'', ''TIME'', ''DATETIME'' — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
  * ''TIMESTAMP'' — хранится в виде ''UNIX_TIMESTAMP'', и можно указать автоматически обновлять колонку
  * Сравнивая типы данных ''DATETIME'' и ''TIMESTAMP'', не забываем делать преобразование типов, например:

|<code bash>SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

</code>|

==== Перечисления ====

  * Для перечислений правильно использовать тип ENUM
  * Можно ставить значение по-умолчанию, как и для любой строки
  * В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
  * Количество перечислений ~ 65 тысяч

===== Отладка =====

  * Если запросы тормозят, то можно включить лог для медленных запросов в ''/etc/mysql/my.cnf''
  * А потом оптимизировать запросы через ''EXPLAIN''
  * И наблюдать за запросами удобно через программу **mytop**

===== Работа с backup`ами =====

=== Создаем backup ===

|<code bash>mysqldump -u USER -pPASSWORD DATABASE > /path/to/code/dump.sql

</code>|

=== Создаём структуру базы без данных ===

|<code bash>mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/code/schema.sql

</code>|

Если нужно сделать дамп только одной или нескольких таблиц

|<code bash>mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/code/dump_table.sql

</code>|

=== Создаём бекап и сразу его архивируем ===

|<code bash>mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

</code>|

=== Создание бекапа с указанием его даты ===

|<code bash>mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

</code>|

=== Заливаем бекап в базу данных ===

|<code bash>mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

</code>|

=== Заливаем архив бекапа в базу ===

|<code bash>gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

</code>|

или так

|<code bash>zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

</code>|

=== Создаём новую базу данных ===

|<code bash>mysqladmin -u USER -pPASSWORD create NEWDATABASE

</code>|

Удобно использовать бекап с дополнительными опциями ''-Q'' ''-c'' ''-e'', т.е.

|<code bash>mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/code/dump.sql

</code>|

''-Q'' оборачивает имена обратными кавычками  ''-c'' делает полную вставку, включая имена колонок  ''-e'' делает расширенную вставку.  Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:

|<code bash>mysqlshow -u USER -pPASSWORD

</code>|

А так же можно посмотреть список таблиц базы:

|<code bash>mysqlshow -u USER -pPASSWORD DATABASE

</code>|

Для таблиц **InnoDB** надо добавлять ''--single-transaction'', это гарантирует целостность данных бекапа. \\ Для таблиц **MyISAN** это не актуально, ибо они не поддерживают транзакционность.