Как изменить collation базы - Исправление ошибок и поиск оптимальных решений проблем

Данный материал является переводом оригинальной статьи «MSSQLTips : How to change server level collation for a SQL Server Instance».

Случилось так, что уже после того, как выполнена установка экземпляра SQL Server, вам с опозданием сообщили, что вы должны использовать другой параметр сортировки (Collation) для этого экземпляра. В этой статье пошагово опишем то, как можно изменить настройку параметров сортировки на уровне сервера для существующего экземпляра SQL Server.

Прежде чем идти дальше, давайте обсудим, что используется для настройки параметров сортировки в соответствии с Books Online.

«В параметрах сортировки задаются правила сортировки и сравнения строк символьных данных на основе норм конкретных языков и локалей. Например, в предложении ORDER BY англоговорящий ожидает, что строка символов «Chiapas» появится до «Colima» в порядке возрастания. Однако испаноговорящий в Мексике может ожидать, что слова, начинающиеся с «Ch», появятся в конце списка слов, начинающихся с «C». Параметры сортировки диктуют эти правила сортировки и сравнения. Параметр сортировки Latin_1 будет сортировать «Chiapas» до «Colima» в предложении ORDER BY ASC, тогда как параметр сортировки Traditional_Spanish будет сортировать «Chiapas» после «Colima».

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

Чтобы изменить параметры сортировки SQL Server по умолчанию, вы можете просто перестроить системные базы данных. Когда вы перестраиваете системную базу данных master, системные базы model, msdb и tempdb фактически удаляются и воссоздаются в исходном местоположении. Если в инструкции rebuild указаны новые параметры сортировки, системные базы данных перестраиваются с использованием этой настройки параметров сортировки. Любые пользовательские изменения в этих базах данных будут потеряны, поэтому важно создать резервную копию любой из той информации, которую вы хотите сохранить. Например, у вас могут быть определенные пользователем объекты в базе данных master, запланированные задания в msdb или изменения параметров базы данных по умолчанию в базе данных model. База данных tempdb воссоздается каждый раз при перезапуске SQL Server, поэтому в этой базе данных нет ничего, что нужно сохранять. Изменение параметров сортировки на уровне сервера не изменяет параметров сортировки существующих пользовательских баз данных, но все вновь созданные пользовательские базы данных будут использовать новые параметры сортировки по умолчанию.

ПРИМЕЧАНИЕ: НЕ ДЕЛАЙТЕ ЛЮБЫЕ ИЗМЕНЕНИЯ В РАБОЧЕЙ СРЕДЕ БЕЗ ДОЛЖНЫХ ИСПЫТАНИЙ В СРЕДЕ ТЕСТИРОВАНИЯ

Далее рассмотрим шаги, необходимые для изменения параметров сортировки на уровне сервера на заданном экземпляре SQL Server.

Шаг 1. Проверка текущих параметров сортировки

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

SELECT SERVERPROPERTY(N'Collation')

Как видите, в данном случае установлен порядок сортировки «SQL_Latin1_General_CP1_CI_AS».

Шаг 2. Сохранение системной конфигурации

В нашем примере необходимо изменить текущий порядок сортировки на «SQL_Latin1_General_CP1_CI_AI». Как я уже упоминал, мы должны перестроить наши системные базы данных, чтобы изменить параметры сортировки на уровне сервера и поместить это новое значение параметра сортировки в команду rebuild. Обязательно запишите все настройки уровня сервера до пересоздания системных баз данных, чтобы смочь обеспечить восстановление системных баз данных до их текущих настроек. Запишите все значения конфигурации сервера, выполнив приведенные ниже команды и сохраните вывод. Если это была новая настройка, и вы не внесли никаких изменений в системные базы данных, вам не нужно беспокоиться о сборе этих данных.

SELECT * FROM sys.configurations;
-- OR
EXEC SP_CONFIGURE

Шаг 3. Сохранение сценариев создания объектов

Создайте и подготовьте все сценарии, связанные с заданиями, планами обслуживания, логинами и уровнями доступа. Вы можете создавать сценарии, выбирая все задания в проводнике объектов в SSMS и щелкая правой кнопкой мыши по вашему выбору, затем выберите вариант «script as» для создания сценария для всех заданий. Вы можете сделать аналогичные шаги для генерации сценариев для предупреждений и операторов. Ниже снимок экрана для создания сценариев для всех ваших заданий.

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

Шаг 4. Отсоединение пользовательских БД

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

Шаг 5. Перестроение системных БД

Теперь пришло время перестроить ваши системные базы данных. Эта операция воссоздает вашу базу данных master и все существующие настройки будут сброшены. Выполните команду ниже из командной строки Windows. Обязательно запустите эту команду из каталога, в который вы разместили установочные файлы SQL Server. Как только вы нажмете Enter, появится отдельное окно, чтобы показать вам индикатор выполнения. Как только перестройка будет завершена, это окно исчезнет.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MANVENDRA /SQLSYSADMINACCOUNTS=gouranghariom /SAPWD=M@nVendr4 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI

По завершении операции перестроения, проверьте параметры сортировки сервера, чтобы проверить, выполнено ли это изменение или нет. Как мы видим на скриншоте ниже, параметры сортировки сервера изменились на «SQL_Latin1_General_CP1_CI_AI». На этом этапе мы не можем восстановить какую-либо из системных баз данных, так как это вернет нас обратно к предыдущей настройке параметров сортировки. Поэтому, нам нужно будет использовать сценарии, созданные для воссоздания логинов, заданий и т.д…

Шаг 6. Присоединение пользовательских БД

Прикрепите все пользовательские базы данных, которые были отсоединены на шаге 4. Если у вас есть какие-либо проблемы, взгляните на этот совет: How to fix database attach error in SQL Server 2008R2.

Шаг 7. Обновление пользовательских БД (опционально)

Теперь измените настройки параметров сортировки для всех пользовательских баз данных. Нет необходимости изменять настройки параметров сортировки для пользовательских баз данных, это полностью зависит от ваших требований. Выполните приведенные ниже команды, чтобы изменить настройки параметров сортировки ваших пользовательских баз данных.

ALTER DATABASE DBName collate SQL_Latin1_General_CP1_CI_AI

Иногда команда не выполняется, и вы получаете ошибку:

Msg 5075, Level 16, State 1, Line 1 The object 'CK_xxxx' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.

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

Шаг 8. Восстановление системной конфигурации и сценариев

Теперь запустите все сценарии, созданные на шаге 3, чтобы восстановить задания, предупреждения (alerts), логины, операторы и т.д. Также не забудьте изменить настройки конфигурации на уровне сервера, которые были зафиксированы на Шаге 2. Теперь ваш экземпляр готов использовать новые параметры сортировки уровня сервера.

Источник

Changing SQL Server Collation In Simple Steps

Here is the simpler steps to change the SQL Server Instance Collation, Please note once the collation is changed we may need to modify the query according to the need as the collation it was referring will change.

Steps:

Open the CMD in elevated privilege
Check the services

sc queryex type= service state=all | find /i"SQL Server"
Stop SQL Server NET STOP «SQL Server (SQLEXPRESS2008R2)»
Move to Binn Directory from CMD
EXAMPLE: D:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESS2008R2MSSQLBinn
type DIR and Check SQL Server.exe is listed
Apply a New SQL Server Collation

sqlservr -m -T4022
-T3659 -s"SQLEXPRESS2008R2" -q"SQL_Latin1_General_CP1_CI_AS"

[-m] single user admin mode
[-T] trace flag turned on at startup
[-s] sql server instance name
[-q] new collation to be applied

4022

Used to bypass automatically started (startup) procedures, this is a subset of startup option –f.
TIP: Each SP consumes one worker thread while executing so you may prefer to make one startup procedure that calls others.

3659

Trace Flag 3659

After a lot of research, I found a reference to this flag in a script called AddSelfToSqlSysadmin, written by Ward Beattie, a developer in the SQL Server product group at Microsoft.
The script contains a line which suggests that this flag enables logging all errors to error log during server startup.
SQL Server may start in single user mode, if command prompt doesn’t end press ctrlX, this will prompt Y/N to shutdown SQL Server, Choose Y

2018-08-01
17:12:30.25
spid7s      Thedefault collation was successfully change

d.

2018-08-01
17:12:30.26
spid7s      Recovery is complete. This is an informationa

l message only. No user action is required.

2018-08-01
17:12:42.01
Logon       Error: 18461, Severity:14, State:
1.

2018-08-01
17:12:42.01
Logon       Login failed for user 'NT AUTHORITYNETWORK S

ERVICE'. Reason: Server is in single user mode. Only one administrator can conne

ct at this time. [CLIENT: <local
machine>]

2018-08-01
17:13:41.91
Logon       Error: 18461, Severity:14, State:
1.

2018-08-01
17:13:41.91
Logon       Login failed for user 'NT AUTHORITYNETWORK S

ERVICE'. Reason: Server is in single user mode. Only one administrator can conne

ct at this time. [CLIENT: <local
machine>]

Do you wish to shutdown SQL Server (Y/N)? Y
Start SQL and verify the collation

NET START"SQL Server (SQLEXPRESS2008R2)"

D:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLEXPRESS2008R2MSSQLBinn>

NET START"SQL Server (SQLEXPRESS2008R2)"

The SQL Server (SQLEXPRESS2008R2) service is starting.

The SQL Server (SQLEXPRESS2008R2) service was started successfully.

More Less

Источник

I am contributing here, as the OP asked:

How to change collation of database, table, column?

The selected answer just states it on table level.

Changing it database wide:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Changing it per table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Good practice is to change it at table level as it’ll change it for columns as well. Changing for specific column is for any specific case.

Changing collation for a specific column:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Nilpo

4,5871 gold badge24 silver badges39 bronze badges

answered Aug 17, 2016 at 11:51

Nabeel AhmedNabeel Ahmed

17.8k4 gold badges56 silver badges61 bronze badges

You need to either convert each table individually:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4

(this will convert the columns just as well), or export the database with latin1 and import it back with utf8mb4.

terales

2,95621 silver badges33 bronze badges

answered Aug 18, 2009 at 14:14

QuassnoiQuassnoi

407k90 gold badges609 silver badges610 bronze badges

You can run a php script.

               <?php
                   $con = mysql_connect('localhost','user','password');
                   if(!$con) { echo "Cannot connect to the database ";die();}
                   mysql_select_db('dbname');
                   $result=mysql_query('show tables');
                   while($tables = mysql_fetch_array($result)) {
                            foreach ($tables as $key => $value) {
                             mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
                       }}
                   echo "The collation of your database has been successfully changed!";
                ?>

answered Oct 4, 2012 at 6:23

hkaserahkasera

2,0883 gold badges23 silver badges32 bronze badges

To change collation for tables individually you can use,

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

To set default collation for the whole database,

ALTER DATABASE  `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

or else,

Goto PhpMyAdmin->Operations->Collation.

There you an find the select box which contains all the exsiting collations. So that here you can change your collation. So here after database table will follows this collation while you are creating new column . No need of select collation while creating new columns.

answered Jan 16, 2013 at 8:05

jeevajeeva

1,5532 gold badges15 silver badges24 bronze badges

The following query will generate ALTER queries that change the collation for all appropriate columns in all tables to a certain type (utf8_general_ci in my example below).

SELECT concat
        (
            'ALTER TABLE ', 
                t1.TABLE_SCHEMA, 
                '.', 
                t1.table_name, 
                ' MODIFY ', 
                t1.column_name, 
                ' ', 
                t1.data_type, 
                '(' , 
                    CHARACTER_MAXIMUM_LENGTH, 
                ')', 
                ' CHARACTER SET utf8 COLLATE utf8_general_ci;'
        )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'you_db_name_goes_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

answered Mar 14, 2016 at 1:26

Parampal PooniParampal Pooni

2,9088 gold badges32 silver badges40 bronze badges

Generates query to update each table and column of each table.
I have used this to some of my projects before and was able to solved most of my COLLATION problems. (especially on JOINS)

To use, just export results to delimited text (probably new line ‘n’)

EACH TABLE

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, 
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
       AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND TABLE_TYPE LIKE 'BASE TABLE'

EACH COLUMN

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ', 
              DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL 
       OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH, 
                                         ')') 
       ), ' COLLATE utf8mb4_unicode_ci;') AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE 
            FROM   INFORMATION_SCHEMA.TABLES 
            WHERE  INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = 
                   INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA 
                   AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = 
                       INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
            LIMIT  1) LIKE 'BASE TABLE' 
       AND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */

gurkan

8544 gold badges15 silver badges24 bronze badges

answered Feb 27, 2021 at 9:04

SelSel

1411 silver badge5 bronze badges

If you run phpMyAdmin >> select database >> select table >> go to «Operations» tab >> in «Table options» section >> you can pick Collation from the drop down list >> and once you press {Go} at the top of the screen you will see a message:

Your SQL query has been executed successfully

and a script

ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci

But it will NOT change the collations of existing columns.
To do so you can use this script (this one also came from phpMyAdmin)

ALTER TABLE  `tableName` CHANGE  `Name`  `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

answered Sep 4, 2015 at 4:34

Just run this SQL to convert all database tables at once. Change your COLLATION and databaseName to what you need.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="databaseName"
AND TABLE_TYPE="BASE TABLE";

answered Oct 23, 2017 at 15:13

DzintarsDzintars

1,30020 silver badges27 bronze badges

Better variant to generate SQL script by SQL request. It will not ruin defaults/nulls.

SELECT concat
    (
        'ALTER TABLE ', 
            t1.TABLE_SCHEMA, 
            '.', 
            t1.table_name, 
            ' MODIFY ', 
            t1.column_name, 
            ' ', 
            t1.column_type,
            ' CHARACTER SET utf8 COLLATE utf8_general_ci',
            if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
            if(t1.column_default is not null, concat(' DEFAULT '', t1.column_default, '''), ''),
            ';'
    )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'your_table_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

answered Nov 4, 2019 at 13:37

Max IvanovMax Ivanov

1211 silver badge3 bronze badges

You can change the CHARSET and COLLATION of all your tables through PHP script as follows. I like the answer of hkasera but the problem with it is that the query runs twice on each table. This code is almost the same except using MySqli instead of mysql and prevention of double querying. If I could vote up, I would have voted hkasera’s answer up.

<?php
$conn1=new MySQLi("localhost","user","password","database");
if($conn1->connect_errno){
    echo mysqli_connect_error();
    exit;
}
$res=$conn1->query("show tables") or die($conn1->error);
while($tables=$res->fetch_array()){
    $conn1->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") or die($conn1->error);
}
echo "The collation of your database has been successfully changed!";

$res->free();
$conn1->close();

?>

answered Feb 2, 2015 at 5:36

mtmehdimtmehdi

611 silver badge6 bronze badges

You can simple add this code to script file

//Database Connection
$host = 'localhost';
$db_name = 'your_database_name';
$db_user =  'your_database_user_name';
$db_pass = 'your_database_user_password';

$con = mysql_connect($host,$db_user,$db_pass);

if(!$con) { echo "Cannot connect to the database ";die();}

  mysql_select_db($db_name);

  $result=mysql_query('show tables');

  while($tables = mysql_fetch_array($result)) {
    foreach ($tables as $key => $value) {
    mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
  }
}

echo "The collation of your database has been successfully changed!";

answered May 5, 2016 at 11:41

Chandra KumarChandra Kumar

4,0821 gold badge17 silver badges25 bronze badges

I read it here, that you need to convert each table manually, it is not true. Here is a solution how to do it with a stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS changeCollation$$

-- character_set parameter could be 'utf8'
-- or 'latin1' or any other valid character set
CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_table_name varchar(255) DEFAULT "";
DECLARE v_message varchar(4000) DEFAULT "No records";

-- This will create a cursor that selects each table,
-- where the character set is not the one
-- that is defined in the parameter

DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()
AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');

-- This handler will set the value v_finished to 1
-- if there are no more rows

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;

OPEN alter_cursor;

-- Start a loop to fetch each rows from the cursor
get_table: LOOP

-- Fetch the table names one by one
FETCH alter_cursor INTO v_table_name;

-- If there is no more record, then we have to skip
-- the commands inside the loop
IF v_finished = 1 THEN
LEAVE get_table;
END IF;

IF v_table_name != '' THEN

IF v_message = 'No records' THEN
SET v_message = '';
END IF;

-- This technic makes the trick, it prepares a statement
-- that is based on the v_table_name parameter and it means
-- that this one is different by each iteration inside the loop

SET @s = CONCAT('ALTER TABLE ',v_table_name,
' CONVERT TO CHARACTER SET ', character_set);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET v_message = CONCAT('The table ', v_table_name ,
' was changed to the default collation of ', character_set,
'.n', v_message);

SET v_table_name = '';

END IF;
-- Close the loop and the cursor
END LOOP get_table;
CLOSE alter_cursor;

-- Returns information about the altered tables or 'No records'
SELECT v_message;

END $$

DELIMITER ;

After the procedure is created call it simply:

CALL changeCollation('utf8');

For more details read this blog.

Seybsen

14.7k4 gold badges40 silver badges71 bronze badges

answered Jun 9, 2015 at 13:07

András OttóAndrás Ottó

7,5471 gold badge28 silver badges38 bronze badges

My solution is a combination of @Dzintars and @Quassnoi Answer.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 ;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<your-database>"
AND TABLE_TYPE="BASE TABLE";

By using CONVERT TO, this generates a scripts, which converts all the Tables of <your-database> to your requested encoding. This also changes the encoding of every column!

answered Nov 27, 2018 at 23:22

if you want to update the default charset on a schema:

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

answered Jul 31, 2017 at 3:44

Mircea StanciuMircea Stanciu

3,5953 gold badges34 silver badges37 bronze badges

I used the following shell script. It takes database name as a parameter and converts all tables to another charset and collation (given by another parameters or default value defined in the 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
)

answered Oct 11, 2017 at 10:52

Quick way — export to SQL file, use search and replace to change the text you need to change. Create new database, import the data and then rename the old database and the new one to the old name.

answered Nov 9, 2015 at 14:25

To change the collation of all fields in all tables of a database at once:

I was just adding another loop for the fields within the tables to the solution via Php before mentioned. This has helped, all fields in the tables are also converted.

<?php
$con = mysql_connect('localhost','user','pw');
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db('database_name');
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $table) {                   // for each table

    $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
    echo "n".$sql;
    mysql_query($sql);

    $sql = "show fields in ".$table." where type like 'varchar%' or type like 'char%' or type='text' or type='mediumtext';";
    $rs2=mysql_query($sql);
    while( $rw2 = mysql_fetch_array($rs2) ){            // for each field in table

        $sql = "ALTER TABLE `".$table."` CHANGE `".$rw2['Field']."` `".$rw2['Field']."` ".$rw2['Type']." CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
        echo "n".$sql;
        mysql_query($sql);

    } 


}
}
echo "The collation of your database has been successfully changed!";

?>}

answered May 1, 2020 at 21:42

Note, after changing the charset for database/table/column, you might need to actually convert the existing data (if you see, for example, something like «Ù…Ø·Ù„ÙˆØ¨ ØªÙˆØ±ÙŠØ¯ Ø¬Ù») with something like this:

update country set name = convert(cast(convert(name using latin1) as binary) using utf8), cn_flag = convert(cast(convert(cn_flag using latin1) as binary) using utf8), and so on..

While for converting database, tables and fields, I would suggest this answer from this thread which would generate a big set of queries that you will just copy at paste, here I couldn’t find an automatic solution yet.
Also be warned, if you will convert the same field twice you will get unrecoverable question marks: «???». You will also get this question marks if you will convert data before converting fields/tables.

answered Feb 11, 2021 at 11:57

user109764user109764

5285 silver badges11 bronze badges

I had to change the collates of all databases, tables and columns in a cluster with many bases.

I used a script running on php 8.1 and mysql 8.0

function changeCollate() {
    $databases = $this->fetchQueryToArray("SHOW DATABASES LIKE 'nova_%'")->rows;
    foreach ($databases as $value) {
        $db = $value['Database (nova_%)'];
        $this->LOG("-- banco de dados --- " . $db);
        $this->exeQuery("ALTER DATABASE `$db` COLLATE utf8mb4_0900_ai_ci;");
        $this->exeQuery("use $db");
        $tables = $this->fetchQueryToArray("SHOW tables")->rows;
        foreach ($tables as $table) {
            $tb_name = $table["Tables_in_$db"];
            $this->exeQuery("ALTER TABLE `$tb_name` COLLATE utf8mb4_0900_ai_ci;");
            $QUERY = "ALTER TABLE `$db`.`$tb_name`n";
            $columns = $this->fetchQueryToArray("SHOW FULL COLUMNS FROM $tb_name WHERE Type LIKE 'varchar%' OR Type = 'text' OR Type like 'enum%' OR Type = 'longtext' OR Type = 'mediumtext'")->rows;
            foreach ($columns as $column) {
                $QUERY .= "CHANGE `{$column['Field']}` `{$column['Field']}` {$column['Type']} COLLATE 'utf8mb4_0900_ai_ci'";
                $QUERY .= ($column['Null'] == 'YES') ?  " NULL" : " NOT NULL";
                if ($column['Default']) $QUERY .= " DEFAULT '{$column['Default']}'";
                if ($column['Comment']) $QUERY .= " COMMENT '{$column['Comment']}'";
                $QUERY .= ",n";
            }
            if ($QUERY == "ALTER TABLE `$db`.`$tb_name`n") continue;
            $QUERY = substr($QUERY, 0, -2) . ";nn";
            $this->exeQuery($QUERY);
        }
    }
}

marc_s

722k173 gold badges1320 silver badges1443 bronze badges

answered Jan 9 at 20:29

I’ve just written a bash script to find all tables in a given database and covert them (and its columns).

Script is available here: https://github.com/Juddling/mysql-charset

answered Apr 19, 2019 at 2:34

JuddlingJuddling

4,5347 gold badges33 silver badges40 bronze badges

Источник

Exporting all of the data (including logins, linked servers, SQL Agent jobs, DB Mail settings, etc), and rebuilding the instance-level data, plus reloading all of the user data, is a lot of work. And, even after all of that, there’s still no guarantee that you can update a database’s default collation via ALTER DATABASE because there are several conditions that will prevent the operation from completing (please see the «Changing the Database Collation» section of the ALTER DATABASE documentation for details).

There is, however, an undocumented method that is much easier. The main drawback being that it’s unsupported. This is not to say that anything will go wrong, just that if something does, Microsoft won’t help fix it (because they never guaranteed that it would work).

The method I speak of is running sqlservr.exe with the -q {new_collation_name} switch. There’s a little more to it than that, but that is the basic idea. This method simply updates the system meta-data, which has benefits and consequences, the main ones being:

BENEFITS

pretty fast
bypass most restrictions that prevent ALTER DATABASE from working
likely far more accurate than any script that people have come up with over the years to drop and recreate objects

DRAWBACKS

unsupported if something goes wrong
VARCHAR data can change, IF the code page is different between the old and new collations, and characters with values of 128 — 255 (0x80 — 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 — 127 which are not in any danger, even if the code page changes.
User-Defined Table Types (UDTTs) are skipped and need to be updated manually.

For a detailed description of what the sqlservr.exe -q method does and does not do (including details on how collations work at the various levels, and potential issues to watch out for), please see my post:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

To change only the instance (including the system databases: master, model, msdb, and tempdb) and one or more databases (but not all databases), simply detach the database(s) that you want to exclude from this operation, and then re-attach them once the collation update completes.

Источник

BENEFITS

pretty fast
bypass most restrictions that prevent ALTER DATABASE from working
likely far more accurate than any script that people have come up with over the years to drop and recreate objects

DRAWBACKS

unsupported if something goes wrong
VARCHAR data can change, IF the code page is different between the old and new collations, and characters with values of 128 — 255 (0x80 — 0xFF) exists, and those characters do not exist as the same character with the same value on the new code page. So the potential is there for data loss, and your data needs to be research first to ensure that this condition does not exist. But, this also means that there are plenty of cases with only characters having values of 0 — 127 which are not in any danger, even if the code page changes.
User-Defined Table Types (UDTTs) are skipped and need to be updated manually.

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

Источник