Таблиці:
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' COLLATE=utf8_bin;') AS cmd
FROM (SELECT table_schema,
table_name,
table_collation
FROM information_schema.tables t
WHERE table_schema = 'zabbix'
AND table_collation != 'utf8_bin') AS tables
ORDER BY cmd;Поля:
SELECT CONCAT('ALTER TABLE ',
table_schema,
'.',
table_name,
' MODIFY COLUMN ',
column_name,
' ',
column_type,
' CHARACTER SET utf8 COLLATE utf8_bin',
defaultval,
nullable,
';') AS cmd
FROM (SELECT table_schema,
table_name,
column_name,
collation_name,
character_set_name,
column_type,
column_default,
CASE
WHEN column_default IS NULL THEN ''
ELSE CONCAT(' DEFAULT ''', column_default, ''' ')
END AS defaultval,
CASE
WHEN is_nullable = 'NO' THEN ' NOT NULL '
ELSE ''
END AS nullable
FROM information_schema.columns
WHERE table_schema = 'zabbix'
AND ( collation_name != 'utf8_bin'
OR character_set_name != 'utf8' )) AS cols
ORDER BY cmd;
