MySQL

Кодировки

UTF-8 -это кодировка переменной длины.

• Mysql utf8mb3 - остальной мир называет сломанным utf-8 (требует не более 3 байт на символ), уже deprecated
• Mysql utf8mb4 - остальной мир называет utf-8 (требует не более 4 байт на символ)
• Mysql utf8 - алиас на Mysql utf8mb3 (в будущем планируется заменить utf8 сокращение на utf8mb4, оставив utf8mb3 для совместимости)

Почему так вышло

Изначально в utf8 было от 1 до 6 байт на символ, mysql решили что не будут поддерживать всё сверх 3 байта, т.е. заведомо ограниченная поддержка (назвав у себя это utf8). Затем utf8 сократили до максимум 4 байт, а в mysql добавили поддержку всех 4 байт utf8 назвав это utf8mb4. 

Поэтому например varchar(20) в кодировке Mysql utf8 использует 60 байт, а в Mysql utf8mb4 требует 80 байт

Рассчитаем максимальную длину поля varchar в разных кодировках:

• utf8_general_ci = 767/3 = 255 символов
• utf8mb4_unicode_ci = 767/3 = 191 символ

Зачем нужен 4-ый байт

Для спец. символов (например смайлы).

UTF-8 по умолчанию 

Указать в файле my.cnf

в секции [client]:

default-character-set = utf8mb4

в секции [mysqld]:

character-set-server=utf8mb4 
collation-server=utf8mb4_unicode_ci 
init-connect="SET NAMES utf8mb4" 
skip-character-set-client-handshake

секции [mysql]:

default-character-set = utf8mb4

в секции [mysqldump]:

default-character-set = utf8mb4

не забываем перезагрузить mysql.

Как посмотреть текущую кодировку

Обо всех таблицах:

SELECT * FROM information_schema.SCHEMATA 
WHERE schema_name = "schemaname";

Об одной таблице:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

или так (команда "desc tablename" не показывает COLLATION NAME):

select * from information_schema.COLUMNS WHERE table_schema=DATABASE() AND TABLE_NAME='tablename';

О колонках одной таблицы:

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

или так:

SHOW FULL COLUMNS FROM tablename;

В рамках текущего подключения:

SELECT @@character_set_database, @@collation_database;

Просмотр всех поддерживаемых сопоставлений:

SHOW COLLATION;
SHOW VARIABLES LIKE 'collation%';

Как изменить существующие настройки

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- кому-то пришлось переехать с MyISAM на InnoDB: ALTER TABLE tablename ENGINE=InnoDB TRANSACTIONAL=default
ALTER TABLE tablename CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Источник: 1 - 2 - 3 - 4 - 5 - 6

Размер таблиц

SELECT
  TABLE_SCHEMA AS `Database`,
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Чтобы посмотреть только по одной базе, добавьте:

WHERE TABLE_SCHEMA = "bookstore"

Чтобы посмотреть только по одной таблице, добавьте:

AND TABLE_NAME = "book"

Просмотр всех только InnoDB таблиц (ведь Engine указывается для таблиц, а не для баз данных (схем)):

SELECT * FROM information_schema.INNODB_SYS_TABLESPACES;

Глобальные переменные

Как изменить глобальные переменные настроек без перезапуска MySQL:

SET GLOBAL names utf8;
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=ON;
SET GLOBAL default_storage_engine = 'InnoDB';

Как проверить изменения:

SHOW VARIABLES LIKE 'innodb_large_prefix';
SHOW VARIABLES LIKE 'innodb_file_format';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_default_row_format';

Как измерить длинну строки

• LENGTH -  количество байт
• CHAR_LENGTH - количество символов

например для слова "яz" LENGTH=3 а CHAR_LENGTH=2

Команды

mysqlcheck -u root -pPWD DB_NAME TABLE_NAME -r - починить таблицу

mysqlcheck -u root -pPWD DB_NAME TABLE_NAME -o - оптимизировать таблицу

Удачки!