Как преобразовать все таблицы в базе данных в одну сортировку?

Я получаю ошибку:

незаконное сочетание параметров сортировки (utf8_general_ci,неявное) и (utf8_unicode_ci, неявное) для операции '='"

Я попытался изменить обе таблицы вручную на utf8_general_ci,IMPLICIT но я все еще получаю ошибки.

есть ли способ преобразовать все таблицы в utf8_general_ci,IMPLICIT и покончим с этим?

8 53
sql mysql

8 ответов:

вам нужно выполнить инструкцию 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";

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

Надеюсь, Что Помогает!

117

лучший вариант, чтобы изменить также параметры сортировки столбцов 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 перед запуском сценария bunch используйте 

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 пытается сопоставить значения данных, но если наборы символов несовместимы, может произойти потеря данных. "

EDIT: специально с перечислением типа столбца, он просто разбивает полностью набор перечислений (даже если нет специальных символов) https://bugs.mysql.com/bug.php?id=26731

44

Ниже приведен более точный запрос. Я даю пример, как преобразовать его в 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"
15

предложение@Namphibian очень помогло мне...
пошел немного дальше, хотя и добавил столбцы и представления в скрипт

просто введите имя вашей схемы ниже, и он сделает все остальное

-- 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 TABLE_SCHEMA=@MY_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 C.TABLE_SCHEMA=@MY_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 V.TABLE_SCHEMA=@MY_SCHEMA
    AND T.TABLE_TYPE="VIEW";
12

вы можете использовать этот скрипт:

#!/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
5

Если вы хотите скопировать и вставить скрипт:

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 на имя базы данных.

0

Это моя версия сценария bash. Он принимает имя базы данных в качестве параметра и преобразует все таблицы в другую кодировку и параметры сортировки (заданные другими параметрами или значением по умолчанию, определенным в скрипте).

#!/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=""
CHARSET=""
COLL=""

[ -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
)
0

принимая ответ от @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=""
PW=""
CHARSET=""
COLL=""

[ -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"
0