Измените кодировку и сопоставление во всех столбцах во всех таблицах MySQL

Мне нужно выполнить эти операторы во всех таблицах для всех столбцов.

alter table table_name charset=utf8;
alter table table_name alter column column_name charset=utf8;

Можно ли как-нибудь автоматизировать это внутри MySQL? Я бы предпочел избегать использования mysqldump

Обновление: Ричард Броноски показал мне путь :-)

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

alter table DBname.DBfield CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Сумасшедший запрос для генерации всех остальных запросов:

SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';

Я хотел выполнить его только в одной базе данных. Выполнение всего за один проход занимало слишком много времени. Оказалось, что он генерировал один запрос на поле для каждой таблицы. И нужен был только один запрос для каждой таблицы (особенный, чтобы спасти). Как я понял, получение вывода в файл.

Как сгенерировать вывод в файл:

mysql -B -N --user=user --password=secret -e "SELECT distinct CONCAT( 'alter table ', TABLE_SCHEMA, '.', TABLE_NAME, '  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'DBname';" > alter.sql

И, наконец, выполнить все запросы:

mysql --user=user --password=secret < alter.sql

Спасибо, Ричард. Ты мужчина!

Во-первых, не верьте мне на слово! Проверьте мое предложение с помощью этого:

select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;

Если вы довольны результатом, удалите предложения о ограничениях и сохраните вывод в SQL-скрипт или, пофантазируйте, и направьте вывод непосредственно в mysql, аналогично тому, что я демонстрирую Вот. Это выглядело бы так:

mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret

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

Фактически, вы можете использовать CONVERT TO для таблицы, чтобы преобразовать все столбцы в этой таблице в кодировку и сопоставление.

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';

Кроме того, для меня имеет смысл выбрать фактическую базу данных, в которой вы хотите это сделать. Итак, это:

... WHERE TABLE_SCHEMA = 'databasename';

а не это:

... WHERE TABLE_SCHEMA != 'information_schema';

Но я думаю, если ты действительно хотел сделать это на всех столах, вы могли бы использовать первое. Хотя мне кажется, это немного тяжеловато. :)

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

SELECT CONCAT(  'ALTER TABLE ',  `TABLE_NAME` ,  ' CHANGE `',  `COLUMN_NAME` ,  '` `',`COLUMN_NAME` ,  '` ',  `DATA_TYPE` ,  '(',  `CHARACTER_MAXIMUM_LENGTH` ,  ') CHARACTER SET utf8 COLLATE utf8_swedish_ci ;' ) FROM  `COLUMNS` WHERE  `TABLE_SCHEMA` =  <schema> AND  `COLLATION_NAME` !=  'utf8_swedish_ci' ORDER BY  `TABLE_NAME` ,  `ORDINAL_POSITION` ;

Вы можете использовать information_schema базу данных, чтобы найти столбец и таблицу, которые необходимо изменить. Вы можете найти их с помощью:

SELECT table_name, column_name FROM information_schema.`COLUMNS`
WHERE table_schema='your database' AND collation_name LIKE 'latin%';

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

В итоге я сделал следующее:

сбросил базу данных в файл sql, используя mysqldump db_name > db_name.sql
замените кодировку в файле sql с помощью awk sed -i -e 's/old_charset/new_charset/g' db_name.sql
импортировать этот новый файл базы данных mysql < db_name

Я бы не стал использовать это в продакшене, но мне это нужно в локальной среде разработки.

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

Помните, что если вы конвертируете несовместимые кодировки, это может испортить ваши данные.