blocks|key|1589309|text|ALTER+DATABASE+db_name+DEFAULT+CHARACTER+SET+utf8+COLLATE+utf8_general_ci|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1589310|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
</code></pre>

blocks|key|1803901|text|您需要了解数据库/表/列级别的字符集/排序规则设置。列级设置优先于其他设置。因此，我包含了一些命令，您可以使用这些命令在数据库的每个级别执行这些更改。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1803902|1803903|检查您的当前配置(数据库)：|1803904|SHOW+CREATE+DATABASE+db_name;|offset|length|style|CODE|1803905|检查您的当前配置(表)：|1803906|SHOW+TABLE+STATUS+WHERE+name='tbl_name'|1803907|检查您的当前配置(列)：|1803908|SHOW+FULL+COLUMNS+FROM+tbl_name;|1803909|1803910|更改字符集/排序规则(数据库)：|1803911|ALTER+DATABASE+db_name+DEFAULT+CHARACTER+SET+utf8;|1803912|更改字符集/排序规则(表)：|1803913|ALTER+TABLE+tbl_name+DEFAULT+CHARACTER+SET+utf8;|1803914|更改字符集/排序规则(列)：|1803915|ALTER+TABLE+tbl_name+CONVERT+TO+CHARACTER+SET+utf8;|1803916|entityMap^0|0|0|0|0|T|0|0|0|13|0|0|0|W|0|0|0|0|1E|0|0|0|1C|0|0|0|1F|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|18|8|@]|9|@]|A|$]]|$1|C|3|D|5|6|7|19|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|1A|8|@$G|1B|H|1C|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|1D|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1E|8|@$G|1F|H|1G|I|J]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1H|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1I|8|@$G|1J|H|1K|I|J]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1M|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1N|8|@$G|1O|H|1P|I|J]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1R|8|@$G|1S|H|1T|I|J]]|9|@]|A|$]]|$1|11|3|12|5|6|7|1U|8|@]|9|@]|A|$]]|$1|13|3|14|5|6|7|1V|8|@$G|1W|H|1X|I|J]]|9|@]|A|$]]|$1|15|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|16|$]]

You need to be aware of the character-set/collation settings at the database/table/column levels. Column-level settings take precedence over the others. Because of this, I'm including commands you can use to perform these changes at each level of the db.

<hr>

Inspect your current configuration (database):

<code>SHOW CREATE DATABASE db_name;</code>

Inspect your current configuration (table):

<code>SHOW TABLE STATUS WHERE name='tbl_name'</code>

Inspect your current configuration (columns):

<code>SHOW FULL COLUMNS FROM tbl_name;</code>

<hr>

Change the character-set/collation (database):

<code>ALTER DATABASE db_name DEFAULT CHARACTER SET utf8;</code>

Change the character-set/collation (table):

<code>ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8;</code>

Change the character-set/collation (columns):

<code>ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;</code>

blocks|key|1589479|text|请注意，如果您确实只想更改一列的排序规则(我不知道为什么会这样，但谁知道呢)，那么可以使用以下语法将ITEMS表中名为DESCRIPTION的TEXT列更改为UTF-8，binary，non-null：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1589480|ALTER+TABLE+ITEMS+CHANGE+DESCRIPTION+DESCRIPTION+TEXT+CHARACTER+SET+utf8
++++COLLATE+utf8_bin+NOT+NULL;|code-block|syntax|javascript|1589481|UTF-8排序规则本身不区分大小写，但utf8_bin排序规则适用于大多数情况。|1589482|entityMap^0|1E|5|1N|B|1Z|4|0|0|J|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

Note that if you really did want to change the collation for just one column (I can't think why you might, but who knows) then this is the syntax to alter a <code>TEXT</code> column called <code>DESCRIPTION</code> in the <code>ITEMS</code> table to UTF-8, binary, non-null:

<pre><code>ALTER TABLE ITEMS CHANGE DESCRIPTION DESCRIPTION TEXT CHARACTER SET utf8
 COLLATE utf8_bin NOT NULL;
</code></pre>

There isn't a case-sensitive UTF-8 collation per se but the <code>utf8_bin</code> collation works for most cases.

blocks|key|1589493|text|在django中，您必须编写自己的迁移：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1589494|./manage.py+makemigrations+--empty+app_name|code-block|syntax|javascript|1589495|并使用以下sql命令填充空迁移：|1589496|#+-*-+coding:+utf-8+-*-
from+__future__+import+unicode_literals

from+django.db+import+models,+migrations


class+Migration(migrations.Migration):

++++dependencies+=+[
++++++++('app',+'0008_prev_migration'),
++++]

++++operations+=+[
++++++++migrations.RunSQL('ALTER+DATABASE+db_name+DEFAULT+CHARACTER+SET+utf8;'),
++++++++migrations.RunSQL('ALTER+TABLE+tbl_name+DEFAULT+CHARACTER+SET+utf8;'),
++++++++migrations.RunSQL('ALTER+TABLE+tbl_name+CONVERT+TO+CHARACTER+SET+utf8;'),
++++]|1589497|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

In django you must write your own migration: 

<pre><code>./manage.py makemigrations --empty app_name
</code></pre>

And fill empty migration with these sql command like this:

<pre><code># -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

 dependencies = [
 ('app', '0008_prev_migration'),
 ]

 operations = [
 migrations.RunSQL('ALTER DATABASE db_name DEFAULT CHARACTER SET utf8;'),
 migrations.RunSQL('ALTER TABLE tbl_name DEFAULT CHARACTER SET utf8;'),
 migrations.RunSQL('ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;'),
 ]
</code></pre>

I'm having the utf-8 Vs. byte string problems mentioned here: <a href="https://stackoverflow.com/questions/2168816/django-headache-with-simple-non-ascii-string">Django headache with simple non-ascii string</a>

I don't care about case sensitive matching in the MySQL columns, I just always want UTF-8 strings returned because I find it is impossible to deal with byte strings returned for character columns for non-ascii text.

How do I change my MySQL collation type so that UTF-8 strings are always returned through Django?

How do you change the collation type for a MySQL column?

我要看utf-8 Vs。这里提到的字节字符串问题：我不关心MySQL列中区分大小写的匹配，我只希望返回UTF8字符串，因为我发现不可能处理为非ascii文本的字符列返回的字节字符串。如何更改我的MySQL排序规则类型，使其始终通过Django返回UTF8字符串？

问如何更改MySQL列的排序规则类型？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何更改MySQL列的排序规则类型？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何更改MySQL列的排序规则类型？
EN