search

yiisoft / yii2

Yii 2: The Fast, Secure and Professional PHP Framework
http://www.yiiframework.com
BSD 3-Clause "New" or "Revised" License
14.23k stars 6.91k forks source link

Standard MySQL collation: Use utf8_unicode_ci instead? #5119

Closed angelcoding closed 10 years ago

angelcoding commented 10 years ago

Minor point, but is there any reason for using utf8_general_ci collation rather than utf8_unicode_ci in the MySQL table options?

Used here ... https://github.com/yiisoft/yii2/blob/bb4a0c70a1b7f2e4f94ef064b0dcaebb29c46b6c/apps/advanced/console/migrations/m130524_201442_init.php#L12 and here ... https://github.com/yiisoft/yii2/blob/a2c483fc19a0d46dcefd5e97293cde55ee552ecf/framework/rbac/migrations/m140506_102106_rbac_init.php#L39

There's some good arguments for using utf8_unicode_ci here ... http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

If it's generally considered better to use utf8_unicode_ci then it would be good to set a standard for others to follow.

cebe commented 10 years ago

There is no reason to use it and according to stackoverflow it is much more reasonable to use unicode as the default. Changed it. Thanks for reporing!

superbull commented 9 years ago

how about using utf8mb4 instead of utf8?

CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB
cebe commented 9 years ago

why? afaik this is not installed on all systems.

superbull commented 9 years ago

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html I just found that utf8mb4 only available after MySQL 5.5.3, so just forget my suggestion. :)

zelenin commented 9 years ago

How should I make AR query with case sensitivity? issue: http://stackoverflow.com/questions/3936967/mysql-case-insensitive-select

samdark commented 9 years ago

@zelenin you need case sensitive collation but since there none for UTF, a workaround is required: https://stackoverflow.com/questions/4558707/case-sensitive-collation-in-mysql

zelenin commented 9 years ago

@samdark I found workaround in my link ( column = binary "value" ). Question is "how can I make it in AR?"

samdark commented 9 years ago

I guess it would be using http://www.yiiframework.com/doc-2.0/yii-db-expression.html

zelenin commented 9 years ago

@samdark Yes, it works! Thanks.

CyberPunkCodes commented 8 years ago

@superbull @cebe FWIW: Yii2 doesn't work with utf8mb4 yet. While it is only for MySQL 5.5.3+, it has support for emojis, special math symbols, and many more. MySQL 5.5.3 came out in 2010. It is now 2016. According to Wordpress, they already made the move. Many other frameworks have implemented it, or have topics requesting it. So this is something that someone has to bite the bullet on soon 👍

I tried to tell Yii to use it, but got an error when doing the built in init migration to setup the user table. It gave me this error: 

QLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Is this because Yii migration tells it to create a varchar with a 255 limit?

cebe commented 8 years ago

this is not a limitation of yii but a limitation of mysql which has a maximum length for index keys. if you use utf8mb4 you have 4 bytes per character, instead of 3 bytes with utf8 so you hit the limit with fewer characters:

"As of MySQL 5.5.3, the utf8mb4 character set uses a maximum of four bytes per character" https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html