Как изменить кодировку таблицы mysql

I know there are many settings for a language for a table and a database. I already created the database. I believe when I created it, it was default/LATIN. I want to change everything-I mean......

1) Database default character set and collation:

SELECT @@character_set_database, @@collation_database;

Altered via: ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;

2) Table default character set and collation:

SELECT T.table_name, CCSA.character_set_name 
FROM information_schema.TABLES T, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA 
WHERE CCSA.collation_name = T.table_collation AND T.table_schema = "YOUR_DB";`

Altered via: ALTER TABLE [table_name] CHARACTER SET utf8 COLLATE utf8_general_ci

3) Column character set and collation:

SELECT c.TABLE_NAME, c.COLUMN_NAME, c.CHARACTER_SET_NAME, c.COLLATION_NAME 
FROM information_schema.COLUMNS c
WHERE c.table_schema = "YOUR_DB";`

Altered via: ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

The third one requires that you disable foreign key checks for the data conversion. So putting this all together:

DELIMITER //
CREATE PROCEDURE migrate_charset_to_utf8()
  BEGIN
    DECLARE done TINYINT DEFAULT 0;
    DECLARE curr_table VARCHAR(64);

    DECLARE table_cursor CURSOR FOR
    SELECT T.table_name
      FROM information_schema.TABLES T
      WHERE T.TABLE_TYPE = 'BASE TABLE' AND
        T.TABLE_SCHEMA = 'YOUR_DB';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN table_cursor;

    table_loop: LOOP
      FETCH table_cursor INTO curr_table;
      IF done THEN
        LEAVE table_loop;
      END IF;

      # Convert table data(columns) charset
      SET @sql_str1 = CONCAT("ALTER TABLE ", curr_table, " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
      PREPARE stmt1 FROM @sql_str1;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      # Set table's default charset e.g for new columns added
      SET @sql_str2 = CONCAT("ALTER TABLE ", curr_table, " CHARACTER SET utf8 COLLATE utf8_general_ci");
      PREPARE stmt2 FROM @sql_str2;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;

    END LOOP table_loop;

    CLOSE table_cursor;
  END//

DELIMITER ;


SET @@FOREIGN_KEY_CHECKS = 0;
CALL migrate_charset_to_utf8();
SET @@FOREIGN_KEY_CHECKS = 1;

ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_general_ci;

EDIT: look here instead

Источник


  • Помощь


  • Хостинг


  • Базы данных


  • Как изменить кодировку для всех таблиц в MySQL

Вы установили MySQL, создали таблицы и наполнили их данными, а в ответ отображается «абракадабра»? Дело в неправильно указанной кодировке. Для того чтобы данные отображались корректно, нужно изменить параметр кодировки для каждой таблицы.

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

  1. 1.

    Войдите в phpMyAdmin и выберите необходимую базу данных из списка, кликнув по её названию:

  2. 2.

    В столбце «Сравнение» отображается сопоставление кодировки базы данных:

  3. 3.

    Скопируйте запрос ниже:

    SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET нужная_кодировка COLLATE сопоставление;') as sqlcode
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'имя_базы'
ORDER BY 1

    где:

    • нужная_кодировка — кодировка, которую нужно применить;

    • сопоставление — сопоставление кодировки базы данных (шаг 2 — столбец «Сравнение»);

    • имя_базы — имя базы данных.

  4. 4.

  5. 5.

    Вставьте запрос из шага 3 в окно запроса MySQL и выполните его, нажав Вперёд. В примере ниже мы сформировали список запросов для всех таблиц базы данных, который нужно выполнить, чтобы изменить кодировку на utf8 и на сопоставление кодировки utf8_general_ci.

  6. 6.

    В качестве ответа на запрос появится список запросов для смены кодировки каждой таблицы. Раскройте вкладку Параметры, установите чекбокс напротив пункта Полные тексты и нажмите Вперёд:

  7. 7.

  8. 8.

    Вернитесь на вкладку SQL. Вставьте запросы в окно запроса MySQL и нажмите Вперёд:

Готово, вы успешно изменили кодировку во всех таблицах базы данных.

Спасибо за оценку!
Как мы можем улучшить статью?

Нужна помощь?
Напишите в службу поддержки!

Источник

Приведу пример смены кодировки MySQL базы данных и таблиц.
Перед любыми действиями над важными данными необходимо обязательно сделать резервную копию, например так:

mysqldump -u USER -h localhost -p BASE | gzip -c > backup_base_`date +%Y-%m-%d`.sql.gz

Для теста подключимся к MySQL и создадим пару новых баз данных без указания кодировки и с указанием:

mysql -u root -p
CREATE DATABASE test_db1;
CREATE DATABASE test_db2 CHARACTER SET utf8 COLLATE utf8_general_ci;

Создадим тестовую таблицу в первой базе и посмотрим её кодировку:

USE test_db1;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status like 'users';

Создадим тестовую таблицу во второй базе и посмотрим её кодировку:

USE test_db2;

CREATE TABLE users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

show table status;

Посмотрим также кодировку обеих баз данных:

SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db1";
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = "test_db2";

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

SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "test_db1"
AND table_name = "users"
AND column_name = "firstname";

В моём случае таблица в первой базе была с кодировкой latin1_swedish_ci, так как она является стандартной, а во второй utf8_general_ci так как я её заранее указал.

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

show collation;
show collation like 'utf8%';
show collation like 'latin1%';

Посмотреть существующие базы данных можно так:

show databases;

Посмотреть существующие таблицы в базе:

USE test_db1;
show tables;

Теперь сменим кодировку первой базы и её таблицы на utf8 и сразу проверим:

ALTER DATABASE `test_db1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE test_db1;
ALTER TABLE `test_db1`.`users` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
show table status;

Если нужно изменить кодировку в sql файле, то откроем его в редакторе Notepad++ преобразуем например в UTF-8/без BOM, а также если в начале файла указана кодировка в SET NAMES, изменим её там, после этого можно импортировать файл в базу.

Источник

Переходим с utf8 на utf8mb4 в MySQL.

utf8 или utf8mb4

Если ваша версия СУБД MySQL 5.5.3 и выше, то вам необходимо использовать кодировку utf8mb4, вместо utf8. Об этом упоминается здесь и здесь.

Следовательно, больше нет необходимости использовать ни utf8_general_ci, ни utf8_unicode_ci.

utf8mb4_general_ci или utf8mb4_unicode_ci

В настоящее время для баз данных и таблиц MySQL рекомендуется использовать кодировку utf8mb4_unicode_ci.

Настройка кодировки utf8mb4 для СУБД MySQL

Исходя из вышеизложенного нам необходимо произвести настройку основных параметров кодировки СУБД MySQL.

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

В конфигурационном файле MySQL (my.ini(windows)/my.cnf(Linux)) необходимо изменить кодировку на utf8mb4:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
init_connect ='SET collation_connection = utf8mb4_unicode_ci'
init_connect ='SET NAMES utf8mb4'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Проверяем корректность работы применимых настроек:

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

Результат:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set, 1 warning (0.00 sec)

Кодировка и сравнение для базы данных, таблиц и столбцов в MySQL

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

Для базы данных:

ALTER DATABASE [db_name] CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Для таблицы:

ALTER TABLE [table_name] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Для столбцов:

ALTER TABLE [table_name] CHANGE [column_name] [column_name] VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Восстановление и оптимизация всех таблиц

После обновления версии MySQL сервера и применения действий по смене кодировки и сравнений, необходимо произвести восстановление и оптимизацию всех баз данных и таблиц. Для этого вы можете выполнить следующие запросы для каждой таблицы:

REPAIR TABLE [table_name];
OPTIMIZE TABLE [table_name];

Или с использованием команды mysqlcheck:

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Пример миграции для Yii2

В этом примере мы изменим кодировку для столбца content в таблице post:

/**
* @return void
* @throws yiidbException
*/
public function safeUp()
{
 $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
 Yii::$app->db->createCommand($sql)->execute();
}

/**
* @return void
* @throws yiidbException
*/
public function safeDown()
{
 $sql = "ALTER TABLE `post` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci";
 Yii::$app->db->createCommand($sql)->execute();
}

Источник

If you, like me, do not trust automation, this is how I have handled the problem.

First Stop digging!

Start with altering the default charset of new tables by changing the DB definition(like in all other answers):

ALTER DATABASE database_name 
  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Then generate sql to change the default charset for new columns of all existing tables:

SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci;") as _sql
  FROM information_schema.TABLES
 WHERE table_schema like "database_name" and TABLE_TYPE="BASE TABLE"
 GROUP BY table_schema, table_name ;

Now we can handle the «legacy»

List character datatypes you are using:

select distinct data_type  from information_schema.columns where table_schema = "database_name" and CHARACTER_SET_NAME is not null;

For me that list was «varchar» and «text»

List character_SETS_ in use:

select distinct character_set_name from information_schema.columns where table_schema = "database_name";

This gives me «utf8», «latin1», and «utf8mb4» which is a reason I do not trust automation, the latin1 columns risk having dirty data.

Now you can make a list of all columns you need to update with:

select table_name, column_name, data_type, character_set_name, collation_name
  from information_schema.columns 
 where table_schema = "database_name" and CHARACTER_SET_NAME is not null AND CHARACTER_SET_NAME <> "utf8mb4"
 group by table_name, data_type, character_set_name, collation_name;

Edit: Original syntax above had an error.

Tables containing only utf8 or utf8mb4 could be converted with «CONVERT TO CHARACTER SET» as Mathias and MrJingles describes above, but then you risk MySQL changing the types for you, so you may be better of running «CHANGE COLUMN» instead since that gives you control of exactly what happens.

If you have non-utf8 columns these questions may give inspiration about checking the columns data: https://stackoverflow.com/q/401771/671282
https://stackoverflow.com/q/9304485/671282

Since you probably know what you expect to have in most of the columns something like this will probably handle most of them after modifying the non-ascii chars allowed to suit your needs:

SELECT distinct section FROM table_name WHERE column_name NOT REGEXP '^([A-Za-z0-9åäöÅÄÖ&.,_ -])*$';

When the above did not fit I used the below that have a bit «fuzzier» maching:

SELECT distinct
  CONVERT(CONVERT(column_name USING BINARY) USING latin1) AS latin1,
  CONVERT(CONVERT(column_name USING BINARY) USING utf8) AS utf8
FROM table_name
WHERE CONVERT(column_name USING BINARY) RLIKE CONCAT('[', UNHEX('C0'), '-', UNHEX('F4'), '][',UNHEX('80'),'-',UNHEX('FF'),']') limit 5;

This query matches any two characters that could start an utf8-character, thus allowing you to inspect those records, it may give you a lot of false positives.
The utf8 conversion fails returning null if there is any character it can not convert, so in a large field there is a good chance of it not being useful.

Источник

If you, like me, do not trust automation, this is how I have handled the problem.

First Stop digging!

Start with altering the default charset of new tables by changing the DB definition(like in all other answers):

ALTER DATABASE database_name 
  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Then generate sql to change the default charset for new columns of all existing tables:

SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci;") as _sql
  FROM information_schema.TABLES
 WHERE table_schema like "database_name" and TABLE_TYPE="BASE TABLE"
 GROUP BY table_schema, table_name ;

Now we can handle the «legacy»

List character datatypes you are using:

select distinct data_type  from information_schema.columns where table_schema = "database_name" and CHARACTER_SET_NAME is not null;

For me that list was «varchar» and «text»

List character_SETS_ in use:

select distinct character_set_name from information_schema.columns where table_schema = "database_name";

This gives me «utf8», «latin1», and «utf8mb4» which is a reason I do not trust automation, the latin1 columns risk having dirty data.

Now you can make a list of all columns you need to update with:

select table_name, column_name, data_type, character_set_name, collation_name
  from information_schema.columns 
 where table_schema = "database_name" and CHARACTER_SET_NAME is not null AND CHARACTER_SET_NAME <> "utf8mb4"
 group by table_name, data_type, character_set_name, collation_name;

Edit: Original syntax above had an error.

Tables containing only utf8 or utf8mb4 could be converted with «CONVERT TO CHARACTER SET» as Mathias and MrJingles describes above, but then you risk MySQL changing the types for you, so you may be better of running «CHANGE COLUMN» instead since that gives you control of exactly what happens.

If you have non-utf8 columns these questions may give inspiration about checking the columns data: https://stackoverflow.com/q/401771/671282
https://stackoverflow.com/q/9304485/671282

Since you probably know what you expect to have in most of the columns something like this will probably handle most of them after modifying the non-ascii chars allowed to suit your needs:

SELECT distinct section FROM table_name WHERE column_name NOT REGEXP '^([A-Za-z0-9åäöÅÄÖ&.,_ -])*$';

When the above did not fit I used the below that have a bit «fuzzier» maching:

SELECT distinct
  CONVERT(CONVERT(column_name USING BINARY) USING latin1) AS latin1,
  CONVERT(CONVERT(column_name USING BINARY) USING utf8) AS utf8
FROM table_name
WHERE CONVERT(column_name USING BINARY) RLIKE CONCAT('[', UNHEX('C0'), '-', UNHEX('F4'), '][',UNHEX('80'),'-',UNHEX('FF'),']') limit 5;

This query matches any two characters that could start an utf8-character, thus allowing you to inspect those records, it may give you a lot of false positives.
The utf8 conversion fails returning null if there is any character it can not convert, so in a large field there is a good chance of it not being useful.

Источник

Понравилась статья? Поделить с друзьями:

Читайте также:

  • Как изменить кодировку субтитров srt
  • Как изменить кодировку страницы html
  • Как изменить кодировку системы windows 10
  • Как изменить кодировку сервера mysql
  • Как изменить кодировку сайта на utf 8

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии