为什么Rails的活动记录迁移产生COLLATE utf8_bin上的MySQL VARCHAR列 [英] Why did Rails Active Record migration generate COLLATE utf8_bin on varchar columns of mysql

问题描述

我已经在轨道上运行JRuby钢轨版本3.0.10。我发现，不知怎的，活动记录迁移产生COLLATE utf8_bin上的所有VARCHAR列。
当我做一个节目创建表的用户：

I have running jruby on rails for rails version 3.0.10. I found out that somehow active record migration generate COLLATE utf8_bin on all the varchar column.
when I do a show create table users:

CREATE TABLE `users` (
  `id` int(11) not null AUTO_INCREMENT default NULL,
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin not null default '',
  `remember_created_at` datetime,
  `sign_in_count` int(11) default 0,
  `current_sign_in_at` datetime,
  `last_sign_in_at` datetime,
  `current_sign_in_ip` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin default NULL,
....

任何人有一个想法，为什么，以及如何将其关闭？

Anyone has an idea why and how to turn it off?

推荐答案

在 utf8_bin 整理用来区分大小写的方式比较字符串，通过每个二进制值字符。这可能取决于你的数据库的字符集其归类或使用本地设置。例如，对于MySQL数据库，你可以通过下面的MySQL命令检查你的MySQL数据库的排序模式

The utf8_bin collation is used to compare strings in a case sensitive mode, by the binary value of each character. It may depend on your local settings of your database which charset or collation is used. For example for a MySQL database, you can check the collation mode of your MySQL database by the following MySQL command

mysql> SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci      |
+----------------------+

..或者使用这一个。

..or by using this one..

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

在你的情况下，这些值中的一个应该是 utf8_bin 。数据库的常规值可以在配置文件中设置，大概的my.cnf 为MySQL。为了创建一个MySQL表与特定的引擎，字符集和校对，您可以使用迁移选项：

In your case one of these values should be utf8_bin. The general values for the database can be set in your configuration file, probably my.cnf for MySQL. In order to create a MySQL table with a specific engine, charset or collation you can use migration options:

create_table :users, 
        :options => 'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci'
        ...

根据该文件，在表字符集和校对规则< /一>是MySQL的扩展，可以在标准的SQL没有这样的事情。

According to the documentation, the table character set and collation are MySQL extensions, there are no such things in standard SQL.

这篇关于为什么Rails的活动记录迁移产生COLLATE utf8_bin上的MySQL VARCHAR列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！