mysql - 如何查询日语 on Rails 事件记录？

Question

我想在 Ruby on Rails 上用日语查询。在我当前的代码中，它不起作用并且只返回一个空集。数据库MYSQL 8.0默认运行在docker上。我应该更新 MYSQL 配置吗？

这是我的选择查询和架构。

事件记录查询

@internships = Internship.where("subject LIKE :keyword OR content LIKE :keyword", keyword: params[:keyword]).all

架构

create_table "internships", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci" do |t|
    t.date "start_date"
    t.date "end_date"
    t.integer "employment_number"
    t.bigint "company_id"
    t.text "content"
    t.string "subject"
    t.integer "job_type"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.date "deadline_date"
    t.text "summary"
    t.index ["company_id"], name: "index_internships_on_company_id"
  end

MYSQL 字符集

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | latin1                         |
| character_set_connection | latin1                         |
| character_set_database   | utf8                           |
| character_set_filesystem | binary                         |
| character_set_results    | latin1                         |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

提前谢谢你。

Answer 1

UTF-8

UTF-8 is an encoding for the Unicode character set, which supports pretty much every language in the world.

The only difference comes with sorting your results, different letters might come in a different order in other languages. Also, comparing a to ä might behave differently in another collation.

utf8mb4_unicode_ci

For a BMP character, utf8 and utf8mb4 have identical storage characteristics: same code values, same encoding, same length

For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

所以你最好改变你的
character_set_client、character_set_connection、character_set_results 到 utf8mb4

解决方案:

对于数据库:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

对于表:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

专栏:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

修改配置文件my.cnf

> vim /etc/my.cnf
# 
[client]
default-character-set = utf8mb4

# 
[mysql]
default-character-set = utf8mb4

# 
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

> service mysqld restart