php - 从php代码保存的mysql数据库读取/插入中文数据

Question

我想使用 ruby​​ 读取/插入数据到 mysql 数据库，数据由 php 代码保存到该数据库中。当我读取中文数据时，它显示不正确。它看起来像åˆ~ä½³。但在php页面中，中文数据正确显示为刘佳。

我确认数据库使用 utf-8 字符集 (CHARSET=utf8 COLLATE=utf8_unicode_ci)。

我的 ruby 代码

require 'active_record'

class Student < ActiveRecord::Base
end

ActiveRecord::Base.establish_connection(
  adapter:  'mysql2',
  host:     'xxxx',
  username: 'xxxx',
  password: 'xxxx',
  database: 'xxx_db',
  encoding: 'utf8'
)

puts Student.first.name

它输出一个未知字符串“åˆ～ä½³”。

如何正确读取中文数据并将新的中文记录保存到数据库中？

Answer 1

    puts Student.first.name

It outputs an unknown string "刘佳".

我相信这是因为您用来查看 ruby​​ 程序输出的任何设备(终端窗口？)都没有设置为“UTF-8”(有关如何检查的信息，请参阅下文)。

据我所知，您所做的一切都是正确的:

mysql 文档:( http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html )

Specify character settings per database. To create a database such that its tables will use a given default character set and collation for data storage, use a CREATE DATABASE statement like this:

CREATE DATABASE mydb   
DEFAULT CHARACTER SET utf8   
DEFAULT COLLATE utf8_general_ci;

Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

Applications that use the database should also configure their connection to the server each time they connect. This can be done by executing a SET NAMES 'utf8' statement after connecting. The statement can be used regardless of connection method: The mysql client, PHP scripts, and so forth.

rails 文档:( http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html )

ActiveRecord::Base.assessment_connection()的所有选项如下(注意:encoding的描述):

Options:

:host - Defaults to “localhost”.

:port - Defaults to 3306.

:socket - Defaults to “/tmp/mysql.sock”.

:username - Defaults to “root”

:password - Defaults to nothing.

:database - The name of the database. No default, must be provided.

:encoding - (Optional) Sets the client encoding by executing
            “SET NAMES <encoding>” after connection.


:reconnect - Defaults to false (See MySQL documentation: dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html).

:strict - Defaults to true. Enable STRICT_ALL_TABLES. (See MySQL documentation: dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html)

:variables - (Optional) A hash session variables to send as `SET @@SESSION.key = value` on each database connection. Use the value `:default` to set a variable to its DEFAULT value. (See MySQL documentation: dev.mysql.com/doc/refman/5.0/en/set-statement.html).

:sslca - Necessary to use MySQL with an SSL connection.

:sslkey - Necessary to use MySQL with an SSL connection.

:sslcert - Necessary to use MySQL with an SSL connection.

:sslcapath - Necessary to use MySQL with an SSL connection.

:sslcipher - Necessary to use MySQL with an SSL connection.

(我很难找到这些内容，因此我将所有内容发布给 future 的谷歌搜索者。)

并且，当我在终端窗口中运行以下程序时，例如:

$ r 1.rb

我的终端窗口设置为 UTF-8:

~/ruby_programs$ locale
LANG="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_CTYPE="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_ALL=

...

# encoding: UTF-8
require 'active_record'
require 'mysql2'

class Student < ActiveRecord::Base
end

ActiveRecord::Base.establish_connection(
  adapter:  'mysql2',
  #host:     'localhost',  #this is the default
  #username: 'root',       #this is the default
  #password: '',           #this is the default
  database: 'mydb2',
  encoding: 'utf8'
)

#Insert a record in the db (It shouldn't matter whether a php or a ruby program writes to the database.)
Student.create(
  name: "\u732a",  #Because of the comment at top of the program, this
                   #string will be encoded in UTF-8

  info: "a pig"    #..so will this one.
) 

name = Student.first.name

puts name
name.each_byte{|b| printf "%x \n", b}
puts

...我在终端窗口中看到的输出是一个汉字，与“ pig ”的汉字相比，它完全匹配，后面跟着:

e7 
8c 
aa 

如果你看这里:http://www.fileformat.info/info/unicode/char/732a/index.html ，这些字节组成了 unicode 整数 \u732a 的 UTF-8 编码，它在中文中表示“pig”，也就是插入数据库的字符串中的内容。

无论如何，你应该运行我的程序，如果你得到同样类型的错误，那么就证明是你的终端编码出了问题。