Я получаю сообщение об ошибке:
Недопустимое сочетание сортировок (utf8_general_ci, IMPLICIT) и (utf8_unicode_ci, IMPLICIT) для операции '=' "
Я попытался изменить обе таблицы вручную на utf8_general_ci,IMPLICIT, но я все еще получаю ошибку.
Есть ли способ преобразовать все таблицы в utf8_general_ci,IMPLICIT и сделать с ним?
Вам нужно выполнить инструкцию alter table для каждой таблицы. Заявление будет следовать этой форме:
ALTER TABLE tbl_name
[[DEFAULT] CHARACTER SET charset_name]
[COLLATE collation_name]
Теперь, чтобы получить все таблицы в базе данных, вам нужно выполнить следующий запрос:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDataBaseName"
AND TABLE_TYPE="BASE TABLE";
Итак, теперь MySQL напишет вам код:
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE your_collation_name_here;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="YourDatabaseName"
AND TABLE_TYPE="BASE TABLE";
Вы можете скопировать результаты и выполнить их. Я не тестировал синтаксис, но вы должны быть в состоянии выяснить остальное. Подумайте об этом как о небольшом упражнении.
Надеюсь, что это поможет!
Лучшая опция для изменения также сортировки столбцов varchar внутри таблицы также
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "myschema"
AND TABLE_TYPE="BASE TABLE"
Дополнение, если у вас есть данные с ключом forein на столбце non utf8 перед запуском, куча script использует
SET foreign_key_checks = 0;
Это означает, что глобальный SQL будет для mySQL:
SET foreign_key_checks = 0;
ALTER TABLE `table1` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `table2` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `tableXXX` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
SET foreign_key_checks = 1;
Но будьте осторожны, если согласно документации mysql http://dev.mysql.com/doc/refman/5.1/en/charset-column.html,
Если вы используете ALTER TABLE для преобразования столбца из одного набора символов в другой, MySQL пытается сопоставить значения данных, но если наборы символов несовместимы, может быть потеря данных. "
РЕДАКТИРОВАТЬ: Специально с перечислением типа столбца он просто заканчивается с завершением перечисления (даже если нет специальных символов) https://bugs.mysql.com/bug.php?id=26731
@Намфибийское предложение мне очень помогло...
пошел немного дальше и добавил столбцы и представления к script
просто введите свое имя схемы ниже, и он сделает все остальное
-- set your table name here
SET @MY_SCHEMA = "";
-- tables
SELECT DISTINCT
CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.TABLES
WHERE [email protected]_SCHEMA
AND TABLE_TYPE="BASE TABLE"
UNION
-- table columns
SELECT DISTINCT
CONCAT("ALTER TABLE ", C.TABLE_NAME, " CHANGE ", C.COLUMN_NAME, " ", C.COLUMN_NAME, " ", C.COLUMN_TYPE, " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
FROM INFORMATION_SCHEMA.COLUMNS as C
LEFT JOIN INFORMATION_SCHEMA.TABLES as T
ON C.TABLE_NAME = T.TABLE_NAME
WHERE C.COLLATION_NAME is not null
AND [email protected]_SCHEMA
AND T.TABLE_TYPE="BASE TABLE"
UNION
-- views
SELECT DISTINCT
CONCAT("CREATE OR REPLACE VIEW ", V.TABLE_NAME, " AS ", V.VIEW_DEFINITION, ";") as queries
FROM INFORMATION_SCHEMA.VIEWS as V
LEFT JOIN INFORMATION_SCHEMA.TABLES as T
ON V.TABLE_NAME = T.TABLE_NAME
WHERE [email protected]_SCHEMA
AND T.TABLE_TYPE="VIEW";
Ниже приведен более точный запрос. Я приведу пример, как преобразовать его в utf8
SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;") AS mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="myschema"
AND TABLE_TYPE="BASE TABLE"
Вы можете использовать этот BASH script:
#!/bin/bash
USER="YOUR_DATABASE_USER"
PASSWORD="YOUR_USER_PASSWORD"
DB_NAME="DATABASE_NAME"
CHARACTER_SET="utf8" # your default character set
COLLATE="utf8_general_ci" # your default collation
tables=`mysql -u $USER -p$PASSWORD -e "SELECT tbl.TABLE_NAME FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = '$DB_NAME' AND tbl.TABLE_TYPE='BASE TABLE'"`
for tableName in $tables; do
if [[ "$tableName" != "TABLE_NAME" ]] ; then
mysql -u $USER -p$PASSWORD -e "ALTER TABLE $DB_NAME.$tableName DEFAULT CHARACTER SET $CHARACTER_SET COLLATE $COLLATE;"
echo "$tableName - done"
fi
done
Если вы хотите скопировать-вставить bash script:
var=$(mysql -e 'SELECT CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_czech_ci;") AS execTabs FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="zabbix" AND TABLE_TYPE="BASE TABLE"' -uroot -p )
var+='ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_general_ci;'
echo $var | cut -d " " -f2- | mysql -uroot -p zabbix
Измените zabbix на ваше имя базы данных.
Это моя версия bash script. Он принимает имя базы данных в качестве параметра и преобразует все таблицы в другую кодировку и сортировку (задается другими параметрами или значением по умолчанию, определенным в script).
#!/bin/bash
# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables
DB="$1"
CHARSET="$2"
COLL="$3"
[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"
echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql
echo "USE $DB; SHOW TABLES;" | mysql -s | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
done
)
Сделав еще один шаг от @Petr Stastny, добавим переменную пароля. Я бы предпочел, чтобы на самом деле он использовался как обычный пароль, а не как аргумент, но он работает для того, что мне нужно.
#!/bin/bash
# mycollate.sh <database> <password> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables
DB="$1"
PW="$2"
CHARSET="$3"
COLL="$4"
[ -n "$DB" ] || exit 1
[ -n "$PW" ]
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_bin"
PW="--password=""$PW"
echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql -u root "$PW"
echo "USE $DB; SHOW TABLES;" | mysql -s "$PW" | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$PW" $DB
done
)
PW="pleaseEmptyMeNow"
Исходя из G H, я добавил параметры пользователя и хоста, если вам нужно сделать это на удаленном сервере
#!/bin/bash
# mycollate.sh <database> <user> <password> [<host> <charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables
DB="$1"
USER="$2"
PW="$3"
HOST="$4"
CHARSET="$5"
COLL="$6"
[ -n "$DB" ] || exit 1
[ -n "$USER" ] || exit 1
[ -n "$PW" ] || exit 1
[ -n "$HOST" ] || HOST="localhost"
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"
PW="--password=""$PW"
HOST="--host=""$HOST"
USER="--user=""$USER"
echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$HOST" "$USER" "$PW"
echo "USE $DB; SHOW TABLES;" | mysql "$HOST" "$USER" "$PW" | (
while read TABLE; do
echo $DB.$TABLE
echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql "$HOST" "$USER" "$PW" $DB
done
)
PW="pleaseEmptyMeNow"
Для phpMyAdmin я понял это:
SELECT GROUP_CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" SEPARATOR ' ') AS OneSQLString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="yourtableschemaname"
AND TABLE_TYPE="BASE TABLE"
Просто измените свое имя, и все в порядке.