Как преобразовать весь набор символов и сопоставление базы данных MySQL в UTF-8?

Как я могу преобразовать весь набор символов базы данных MySQL в UTF-8 и сопоставление в UTF-8?

Источник

20 ответов

Решение

Использовать ALTER DATABASE а также ALTER TABLE команды.

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Или, если вы все еще на MySQL 5.5.2 или более ранней версии, которая не поддерживает 4-байтовый UTF-8, используйте utf8 вместо utf8mb4:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Источник

  1. Сделайте резервную копию!

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

    ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

  3. Затем вам нужно будет преобразовать набор символов для всех существующих таблиц и их столбцов. Это предполагает, что ваши текущие данные на самом деле в текущем наборе символов. Если для ваших столбцов задан один набор символов, но ваши данные действительно хранятся в другом, вам нужно будет обратиться к руководству по MySQL, чтобы узнать, как это сделать.

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Источник

В командной строке

Если вы являетесь оболочкой командной строки, вы можете сделать это очень быстро. Просто заполните "dbname":D

DB="dbname"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"

Один вкладыш для простого копирования / вставки

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"
Источник

Вы можете создать sql для обновления всех таблиц:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8 COLLATE utf8_general_ci;   ",
    "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ") 
    AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = your_database_name;

Захватите выход и запустите его.

Ответ Арнольда Дэниелса выше более элегантен.

Источник

Прежде чем продолжить, убедитесь, что вы: завершили полное резервное копирование базы данных!

Шаг 1: Изменения уровня базы данных

  • Определение сопоставления и набора символов вашей базы данных

    SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM 
information_schema.SCHEMATA S
WHERE schema_name = 'your_database_name'
AND
(DEFAULT_CHARACTER_SET_NAME != 'utf8'
    OR
 DEFAULT_COLLATION_NAME not like 'utf8%');

  • Исправление сопоставления для базы данных

    ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Шаг 2: Изменения на уровне таблицы

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

    SELECT CONCAT(
'ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci;  ', 
'ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'your_database_name'
AND
(C.CHARACTER_SET_NAME != 'utf8'
    OR
 C.COLLATION_NAME not like 'utf8%')

  • Настройка сортировки столбцов таблицы и набора символов

Захватите верхний вывод sql и запустите его. (вроде следующего)

ALTER TABLE rma CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_products CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_products CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_report_period CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_report_period CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_reservation CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_reservation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return_history CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE rma_supplier_return_product CHARACTER SET utf8 COLLATE utf8_general_ci;ALTER TABLE rma_supplier_return_product CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

обратитесь к: https://confluence.atlassian.com/display/CONFKB/How+to+Fix+the+Collation+and+Character+Set+of+a+MySQL+Database

Источник

Используйте HeidiSQL. Это бесплатный и очень хороший инструмент БД.

В меню инструментов войдите в редактор Bulk Table

Выберите полную базу данных или выберите таблицы для преобразования,

  • Установите флажок Изменить параметры сортировки по умолчанию: utf8mb4_general_ci
  • установите флажок Преобразовать в кодировку: utf8

казнить

Это преобразует полную базу данных из латинского в utf8 всего за несколько секунд.

Работает как шарм:)

HeidiSQL подключается по умолчанию как utf8, поэтому любые специальные символы теперь должны рассматриваться как символы (ø å), а не как закодированные при проверке данных таблицы.

Настоящая проблема при переходе с латиницы на utf8 заключается в том, чтобы убедиться, что pdo соединяется с кодировкой utf8. Если нет, то вы получите мусорные данные, вставленные в таблицу utf8 и вопросительные знаки повсюду на вашей веб-странице, заставляя вас думать, что данные таблицы не являются utf8...

Источник 
DELIMITER $$  

CREATE PROCEDURE `databasename`.`update_char_set`()  

BEGIN  
 DECLARE done INT DEFAULT 0;  
 DECLARE t_sql VARCHAR(256);  
 DECLARE tableName VARCHAR(128);  
 DECLARE lists CURSOR FOR SELECT table_name FROM `information_schema`.`TABLES` WHERE table_schema = 'databasename';  
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
 OPEN lists;  
 FETCH lists INTO tableName;  
 REPEAT  
    SET @t_sql = CONCAT('ALTER TABLE ', tableName, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci');  
    PREPARE stmt FROM @t_sql;  
    EXECUTE stmt;  
    DEALLOCATE PREPARE stmt;  
 FETCH lists INTO tableName;  
 UNTIL done END REPEAT;  
 CLOSE lists;  
END$$  

DELIMITER ;  

CALL databasename.update_char_set();
Источник

Вдохновленный комментариями @sdfor, вот скрипт bash, который делает эту работу

#!/bin/bash

printf "### Converting MySQL character set ###\n\n"

printf "Enter the encoding you want to set: "
read -r CHARSET

# Get the MySQL username
printf "Enter mysql username: "
read -r USERNAME

# Get the MySQL password
printf "Enter mysql password for user %s:" "$USERNAME"
read -rs PASSWORD

DBLIST=( mydatabase1 mydatabase2 )

printf "\n"


for DB in "${DBLIST[@]}"
do
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
    mysql "$DB" -u"$USERNAME" -p"$PASSWORD" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE `'"$CHARSET"'`;'
) \
| mysql "$DB" -u"$USERNAME" -p"$PASSWORD"

echo "$DB database done..."
done

echo "### DONE ###"
exit
Источник

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

Каждый тип столбца имеет свой соответствующий двоичный тип, как показано ниже:

  1. CHAR => Двоичный
  2. ТЕКСТ => BLOB
  3. TINYTEXT => TINYBLOB
  4. MEDIUMTEXT => MEDIUMBLOB
  5. LONGTEXT => LONGBLOB
  6. VARCHAR => VARBINARY

Например.:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARBINARY;

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] MODIFY [COLUMN_NAME] VARCHAR(140) CHARACTER SET utf8mb4;

Я пробовал в нескольких латинских таблицах, и в нем содержались все диакритические знаки.

Вы можете извлечь этот запрос для всех столбцов, которые делают это:

SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' VARBINARY;'),
CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME,' MODIFY ', COLUMN_NAME,' ', COLUMN_TYPE,' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.columns
WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]')
AND COLUMN_TYPE LIKE 'varchar%'
AND (COLLATION_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf%');

После того, как вы сделаете это для всех ваших столбцов, вы сделаете это для всех таблиц:

ALTER TABLE [TABLE_SCHEMA].[TABLE_NAME] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Чтобы сгенерировать этот запрос для всей вашей таблицы, используйте следующий запрос:

SELECT
CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_COLLATION NOT LIKE 'utf8%'
and TABLE_SCHEMA in ('[TABLE_SCHEMA]');

И теперь, когда вы изменили все свои столбцы и таблицы, сделайте то же самое с базой данных:

ALTER DATABASE [DATA_BASE_NAME] CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;
Источник

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

$conn = mysqli_connect($host, $username, $password, $database);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$alter_database_charset_sql = "ALTER DATABASE ".$database." CHARACTER SET utf8 COLLATE utf8_unicode_ci";
mysqli_query($conn, $alter_database_charset_sql);

$show_tables_result = mysqli_query($conn, "SHOW TABLES");
$tables  = mysqli_fetch_all($show_tables_result);

foreach ($tables as $index => $table) {
  $alter_table_sql = "ALTER TABLE ".$table[0]." CONVERT TO CHARACTER SET utf8  COLLATE utf8_unicode_ci";
  $alter_table_result = mysqli_query($conn, $alter_table_sql);
  echo "<pre>";
  var_dump($alter_table_result);
  echo "</pre>";
}
Источник

Если данные не находятся в одном и том же наборе символов, вы можете рассмотреть этот фрагмент из http://dev.mysql.com/doc/refman/5.0/en/charset-conversion.html

Если столбец имеет недвоичный тип данных (CHAR, VARCHAR, TEXT), его содержимое должно быть закодировано в наборе символов столбца, а не в каком-либо другом наборе символов. Если содержимое закодировано в другом наборе символов, вы можете сначала преобразовать столбец в двоичный тип данных, а затем в недвоичный столбец с нужным набором символов.

Вот пример:

 ALTER TABLE t1 CHANGE c1 c1 BLOB;
 ALTER TABLE t1 CHANGE c1 c1 VARCHAR(100) CHARACTER SET utf8;

Убедитесь, что вы выбрали правильное сопоставление, иначе вы можете получить уникальные конфликты ключей. например, Элеонора и Элеонора могут считаться одинаковыми в некоторых сопоставлениях.

В сторону:

У меня была ситуация, когда определенные символы "ломались" в электронных письмах, даже если они были сохранены в базе данных как UTF-8. Если вы отправляете электронные письма с использованием данных utf8, вы можете также преобразовать свои электронные письма в UTF8.

В PHPMailer просто обновите эту строку: public $CharSet = 'utf-8';

Источник

из utf8 в utf8mb4:

1. показать все наборы символов по умолчанию для БАЗЫ ДАННЫХ:

      SELECT SCHEMA_NAME 'YOUR_DATABASE_NAME', 
default_character_set_name 'charset', 
DEFAULT_COLLATION_NAME 'collation' 
FROM information_schema.SCHEMATA;

2. показать статус всех таблиц (набор символов), сосредоточиться на столбце «сопоставление»:

      use YOUR_DATABASE_NAME;    
SHOW TABLE STATUS ;

3. сгенерировать конвертировать sql: конвертировать базу данных и все таблицы в utf8mb4,utf8mb4_unicode_ci

      USE information_schema;
SELECT CONCAT("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") AS _sql
FROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema UNION
SELECT CONCAT("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") AS _sql  
FROM `TABLES` WHERE table_schema LIKE "YOUR_DATABASE_NAME" AND TABLE_TYPE='BASE TABLE' GROUP BY table_schema, TABLE_NAME 
/*include all columns, commonly don't need this.*/
/*
UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('varchar','char') AND TABLE_TYPE='BASE TABLE' UNION
SELECT CONCAT("ALTER TABLE `",`COLUMNS`.table_schema,"`.`",`COLUMNS`.table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci",IF(is_nullable="YES"," NULL"," NOT NULL"),";") AS _sql 
FROM `COLUMNS` INNER JOIN `TABLES` ON `TABLES`.table_name = `COLUMNS`.table_name WHERE `COLUMNS`.table_schema like "YOUR_DATABASE_NAME" and data_type in ('text','tinytext','mediumtext','longtext') AND TABLE_TYPE='BASE TABLE';
*/

4. запустите сгенерированный sql.

5. Обновите базу данных.

6.проверить:

      SHOW TABLE STATUS ;
Источник 
mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql
cp dump.sql dump-fixed.sql
vim dump-fixed.sql


:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq

mysql -uusername -ppassword < dump-fixed.sql
Источник

Если вы не можете преобразовать свои таблицы или если для вашей таблицы всегда задан какой-то набор символов, отличных от utf8, но вы хотите использовать utf8, лучше всего было бы стереть его и начать заново и явно указать:

create database database_name character set utf8;
Источник

Чтобы изменить кодировку набора символов на UTF-8 для самой базы данных, введите следующую команду в приглашении mysql>. Замените DBNAME именем базы данных:

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;
Источник

Решение командной строки и исключение представлений

Я просто завершаю ответ @Jasny для других, таких как@Brian и я, у кого есть просмотры в нашей базе данных.

Если у вас есть такая ошибка:

ERROR 1347 (HY000) at line 17: 'dbname.table_name' is not of type 'BASE TABLE'

Это потому, что у вас, вероятно, есть просмотры, и вам нужно их исключить. Но при попытке их исключить MySQL возвращает 2 столбца вместо 1.

SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE';
-- table_name1  BASE TABLE
-- table_name2  BASE TABLE

Поэтому мы должны адаптировать команду Ясны с помощью awk для извлечения только 1-го столбца, содержащего имя таблицы.

DB="dbname"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql "$DB" -e "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" --batch --skip-column-names \
    | awk '{print $1 }' \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"

Однострочный для простого копирования / вставки

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" --batch --skip-column-names | awk '{print $1 }' | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"
Источник

Изменить таблицу table_name charset = 'utf8';

Это простой запрос, который я смог использовать в моем случае, вы можете изменить имя_таблицы в соответствии с вашими требованиями.

Источник

Вы также можете использовать инструмент БД Navicat, который делает это проще.

  • Шив.

Щелкните правой кнопкой мыши по вашей базе данных и выберите Свойства базы данных и измените ее в раскрывающемся списке.

введите описание изображения здесь

Источник

Единственное решение, которое сработало для меня: http://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

Преобразование базы данных, содержащей таблицы

mysqldump -uusername -ppassword -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B dbname > dump.sql

cp dump.sql dump-fixed.sql
vim dump-fixed.sql

:%s/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci/
:%s/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/
:wq

mysql -uusername -ppassword < dump-fixed.sql
Источник

Чтобы изменить кодировку набора символов на UTF-8, выполните простые шаги в PHPMyAdmin.

  1. Выберите вашу базу данных СС

  2. Перейти к операциямСС

  3. На вкладке операций в нижнем раскрывающемся меню сортировки выберите желаемую кодировку, например (utf8_general_ci), а также установите флажок (1) изменить все сопоставления таблиц, (2) изменить сопоставления столбцов всех таблиц. и нажмите Go.

СС

Источник
Другие вопросы по тегам