如何将Mysql排序规则更改为土耳其语? [英] How to change Mysql collation to Turkish?

问题描述

我有一个带有utf8字符集和utf8_unicode_ci排序规则的mysql模式.问题是当我想查询一些土耳其语字符时，Mysql无法确定此字符是否为土耳其语.

I have a mysql schema with utf8 charset and utf8_unicode_ci collation. Problem is when I want to query some Turkish characters Mysql can't determine this character is Turkish or not.

在土耳其语中，以大写字母"i"表示的İ"和以大写字母ı"表示的"I".

In Turkish there is "İ" which is capital "i" and "I" which is capital "ı".

SELECT name FROM students where name = "testĞÇ"; //returns "testğç" which is true
SELECT name FROM students where name = "testI"; //Mysql thinks "I" character is capital "i" and returns null

我的学生表数据；

testğç
testı

编辑:我的名称"列的排序规则是utf8_unicode_ci.

My "name" column's collation is utf8_unicode_ci.

SELECT name FROM students where name = "testI" collate utf8_turkish_ci; //still returns null

当我将名称"列的排序规则从utf8_unicode_ci更改为utf8_turkish_ci时，效果很好.但是这一次我需要将所有表列的归类更改为utf8_turkish_ci，但我不知道这会导致数据丢失.

EDIT 2: When I change "name" column's collation from utf8_unicode_ci to utf8_turkish_ci, It works well. But this time I need to change all my table column's collation's to utf8_turkish_ci and I don't know it will cause any loss of data.

推荐答案

我知道您没有指定语言，但是我认为这可以为您提供帮助.当我遇到像您一样的问题时，我在某个地方找到了一个脚本，并通过运行此代码解决了该问题:

I know you didn't specify a language but I think this could help you. I had found a script somewhere when I had the same problem like you and fixed the problem with running this code:

<?php 
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'db_name';
$collation = "utf8_general_ci";
$charset = "utf8";

mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
mysql_query("ALTER DATABASE $dbname COLLATE $collation");
$result = mysql_query("SHOW TABLES"); 
while ($row = mysql_fetch_row($result)) { 
    mysql_query("ALTER TABLE $row[0] COLLATE $collation"); 
    $result1 = mysql_query("SHOW COLUMNS FROM $row[0]"); 
    while ($row1 = mysql_fetch_assoc($result1)) { 
        if (preg_match('~char|text|enum|set~', $row1["Type"])) { 
            mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET $charset"); 
            mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation DEFAULT '$row1[Default]'"); 
            echo $row[0] . "->" . $row1[Field] . "->" . $row1[Type] . " is now UTF8\n";
        }
    }
}
mysql_free_result($result);
?>

它简单地获取所有表并将其排序规则更改为utf8_general_ci，因为它支持大多数可用字符.这样，您新添加的数据将具有正确的字符集.

It simply fetches all tables and changes it's collation to utf8_general_ci as it supports most chars available. Then your newly added datas would have the correct charset.

实现此目的的主要代码是:

The main codes that do the trick are:

对于数据库:

ALTER DATABASE [db_name] COLLATE [collation]

对于单个表:

ALTER TABLE [table_name] MODIFY [column_name] [column_type] CHARACTER SET [charset]
ALTER TABLE [table_name] MODIFY [column_name] [column_type] COLLATE [collation]

这篇关于如何将Mysql排序规则更改为土耳其语?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！