good practice for storing time related objs in mysql with python

I recently wrote a spider for jobs with the scrapy framework, and stored all my items in mysql.

But here the problem comes. If you try to use timestamp type in mysql, several traps may be concerned.

First, how the timestamp being interpreted is affected by your connection timezone.If you do not specify, it may use the mysql server one.But who knows what is the default one.

Second, your mysql client in python layer may not support time_zone param, which is available in mysql c api.Some libraries like MySQL-python==1.2.5 just not support it.
So, if you want to change connection timezone, you may have to specify it explicitly on every sql query.

Third, your mysql database may not be controlled by you, so you would be better to change your logic code timezone.

As for me, i would just use INT for all my time related data, and represent them when necessary.

So, use it explicitly, or just don’t use it.

Or, even better, see discussions on ST.In general,just set your db timezone to UTC, and use local timezone on your logical layers.

mysql set names utf8

背景

最近在看 opencart 的代码,看到 mysql 类的时候,不太理解 query="set names utf8" 具体的意义是什么,那时也没太注意。后来自己写了个简单的数据类从 mysql 抽取数据出来的时候,显示在页面的都是???号,也就是大家常说的乱码了。

想起之前的 set names 操作,于是就在类的__construct 方法里面,加入了 mysql_query(“SET NAMES 'utf8'”,$this->connection);,然后一切都好了。

why?

那为什么 set names ‘utf8’会出现这样的效果?在我从数据库读取和写入数据的时候,中间的编码转换过程到底是什么样的?…… 这些问题涌上心头,还是动手写写测试,查查手册吧~

#### 我的系统参数

  • Ubuntu11.04
  • Mysql 5.1.54 (mysql>SELECT VERSION () 查看)
  • PHP 5.3.5(php -n -v 查看)

####set names utf8 是什么意思
在 CSDN 上面有一篇很经典的文章,讲到这个问题,原来 mysql_query("set names 'utf8'") 会导致 mysql 数据库的几个默认编码值发生变化,分别是 character_set_client、character_set_connection,还有 character_set_results。

那么,为什么这 3 个值到底是什么,为什么会影响字符的编码呢?下面基于各位前辈的资料,综合自己的理解,来讲下 mysql 的字符集问题。

常规字符集和校对

1.MySQL5.1 的特性

- 使用多种字符集(character_set)来存储字符串(比较常见的有utf8、gbk2312...... )
- 使用多种校对规则(charset_collation)来比较字符串
- 在同一台服务器、同一个数据库 或甚至在同一个表中使用不同字符集或校对规则来混合字符串
- 允许定义任何级别的字符集和校对规则

2.Mysql 中的字符集和校对规则字符集变量
在命令行登录 mysql 后,查看到这些变量的运行值:
zhkzyth@ubuntu:~$ mysql -u root -p
Enter password:
// 一些欢迎和版本信息
mysql>show variables like “character_set%”

可以看到,系统内部字符集变量有很多,这里,先简单说下各个变量的涵义,后面会展开讲述各个变量的具体涵义和作用。
character_set_client :这是设置客户端发送查询使用的字符集
character_set_connection :这是设置服务器需要将收到的查询串转换成的字符集
character_set_database:这是设置数据库使用的字符集
character_set_filesystem:这是查询本地文件时候,文件名需要转换的字符集
character_set_results :这是设置服务器要将结果数据转换到的字符集,转换后才发送给客户端
character_set_server:这是设置服务器使用的字符集
character_set_system:这是设置系统使用的字符集
character_set_dir :这个是 mysql 查找字符集的位置
各变量详细介绍:传送门

而通过下面的命令,则可以查看到系统的 collation 字符集变量都有哪些。
zhkzyth@ubuntu:~$ mysql -u root -p
Enter password:
// 一些欢迎和版本信息
mysql>show variables like “collation_%”

由图中我们可以看到,系统默认的校对规则字符集有三个,我们后面会来看看这些变量的意义。

Mysql 的默认字符集和校对

在 Mysql 中,字符集和校对规则有 4 个级别的默认设置:服务器级、数据库级、表级和连接级,下面详细讲述每个级别的涵义。

#### 服务级
在 MySQL 按照如下方法确定服务器字符集和服务器校对规则:
当服务器启动时根据有效的选项设置
在服务器级别,确定方法很简单。当启动 mysqld 时,根据使用的初始选项设置来确定服务器字符集和校对规则。 在 unix 下,比如我的 ubuntu 的系统,就可以在”/etc/mysql/my.cnf” 或”~/.my.conf” 下进行字符集的设置。
在 [client] 下,加入 default-character-set=utf8,保存重启 Mysql 服务就可以进行修改了。

重启后,结果如下:
mysql> show variables like “character_set%”
-> ;
+————————–+—————————-+
| Variable_name | Value |
+————————–+—————————-+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+————————–+—————————-+
8 rows in set (0.10 sec)

这里有几点要注意

1. 使 --default-character-set X 设置字符集,而没有指定 —default-collation 的话,collation 会使用 --default-character-set X 对应的校对规则字符集。比如我设了 X 是 utf8,那么 collation 就会自动设置成 utf8 对应的校对集 utf8_general_ci。
mysql> show variables like “collation%”;
+———————-+——————-+
| Variable_name | Value |
+———————-+——————-+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+———————-+——————-+
3 rows in set (0.00 secs)
2. 你也可以在字符集后面为校对规则添加 default-collation,指定你想要的校对规则字符集。
如果没有指定一个字符集,那就与 --default-character-set=latin1 相同。
当前的服务器字符集和校对规则可以用作 character_set_server 和 collation_server 系统变量的值。在运行时能够改变这些变量的值。
SET NAMES ‘charset_name’
SET CHARACTER SET charset_name

SET NAMES 'x' 等效于
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

SET CHARACTER SET 'x' 等效于
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database; --数据库的collation
(ps:上述命令现在已经不建议使用,请看到后面的描述。)

#### 数据库级
1. 数据库级的字符集和校对
每一个数据库有一个数据库字符集和一个数据库校对规则,它不能够为空。CREATE DATABASE 和 ALTER DATABASE 语句有一个可选的子句来指定数据库字符集和校对规则:
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]

例如:
CREATE DATABASE db_name
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL 这样选择数据库字符集和数据库校对规则:

  • 如果指定了 CHARACTER SET X 和 COLLATE Y,那么采用字符集 X 和校对规则 Y。
  • 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y,那么采用 CHARACTER SET X 和 CHARACTER SET X 的默认校对规则。
  • 否则,采用服务器字符集和服务器校对规则。

如果在 CREATE TABLE 语句中没有指定表字符集和校对规则,则使用数据库字符集和校对规则作为默认值。

2. 表字符集和校对

每一个表有一个表字符集和一个校对规则,它不能为空。为指定表字符集和校对规则,CREATE TABLE 和 ALTER TABLE 语句有一个可选的子句:

CREATE TABLE tbl_name (column_list)
[DEFAULT CHARACTER SET charset_name [COLLATE collation_name]]

ALTER TABLE tbl_name
[DEFAULT CHARACTER SET charset_name] [COLLATE collation_name]

例如:
CREATE TABLE t1 ( … )
DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;

MySQL 按照下面的方式选择表字符集和 校对规则:

  • 如果指定了 CHARACTER SET X 和 COLLATE Y,那么采用 CHARACTER SET X 和 COLLATE Y。
  • 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y,那么采用 CHARACTER SET X 和 CHARACTER SET X 的默认校对规则。
  • 否则,使用数据库字符集和校对规则作为默认值。

如果在列定义中没有指定列字符集和校对规则,则默认使用表字符集和校对规则。表字符集和校对规则是 MySQL 的扩展;在标准 SQL 中没有。

3. 列字符集和校对
每一个 “字符” 列(即,CHAR、VARCHAR 或 TEXT 类型的列)有一个列字符集和一个列校对规则,它不能为空。列定义语法有一个可选子句来指定列字符集和校对规则:
col_name {CHAR | VARCHAR | TEXT} (col_length)
[CHARACTER SET charset_name [COLLATE collation_name]]

例如:
CREATE TABLE Table1
(
column1 VARCHAR(5) CHARACTER SET latin1 COLLATE latin1_german1_ci
);

MySQL 按照下面的方式选择列字符集和校对规则:

  • 如果指定了 CHARACTER SET X 和 COLLATE Y,那么采用 CHARACTER SET X 和 COLLATE Y。
  • 如果指定了 CHARACTER SET X 而没有指定 COLLATE Y,那么采用 CHARACTER SET X 和 CHARACTER SET X 的默认校对规则。
  • 否则,采用表字符集和服务器校对规则。

CHARACTER SET 和 COLLATE 子句是标准的 SQL。

举例子

示例 1:表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;
在这里我们有一个列使用 latin1 字符集和 latin1_german1_ci 校对规则。是显式的定义,因此简单明了。需要注意的是,在一个 latin2 表中存储一个 latin1 列不会存在问题。

示例 2:表和列定义
CREATE TABLE t1
(
c1 CHAR(10) CHARACTER SET latin1
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
这次我们有一个列使用 latin1 字符集和一个默认校对规则。尽管它显得自然,默认校对规则却不是表级。相反,因为 latin1 的默认校对规则总是 latin1_swedish_ci,列 c1 有一个校对规则 latin1_swedish_ci(而不是 latin1_danish_ci)。

示例 3:表和列定义
CREATE TABLE t1
(
c1 CHAR(10)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
我们有一个列使用一个默认字符集和一个默认校对规则。在这种情况下,MySQL 查找表级别来确定列字符集和 校对规则。因此,列 c1 的字符集是 latin1,它的 校对规则是 latin1_danish_ci。

示例 4:数据库、表和列定义
在 mysql 下:
CREATE DATABASE d1
DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
c1 CHAR(10)
);
我们创建了一个没有指定字符集和校对规则的列。我们也没有指定表级字符集和校对规则。在这种情况下,MySQL 查找数据库级的相关设置。(数据库的设置变为表的设置,其后变为列的设置。)因此,列 c1 的字符集为是 latin2,它的 校对规则是 latin2_czech_ci。

连接级的连接字符集和校对

一些字符集和校对规则系统变量与客户端和服务器的交互有关。
考虑什么是一个 “连接”:它是连接服务器时所作的事情。客户端发送 SQL 语句,例如查询,通过连接发送到服务 器。服务器通过连接发送响应给客户端,例如结果集。我所理解的交互过程,不一定对,大致如下图:

对于客户端连接,这样会导致一些关于连接的字符集和 校对规则的问题,这些问题均能够通过系统变量来解决:

  1. 在图中圆圈 1 的位置,表示当查询离开客户端后,服务器使用 character_set_client 变量作为客户端发送的查询中使用的字符集。
  2. 图中 2 的位置表示,服务器接收到查询后,使用 character_set_connection 和 collation_connection 系统变量, 将客户端发送的查询从 character_set_client 系统变量转换到 character_set_connection 。
  3. 图中 3、4、5 的位置表示,当服务器收到数据请求后,Mysql 从数据库中提取数据,这个时候,就会根据数据库,以及表、列本身的编码集进行编码转换。比如,character_set_client 为 utf8,而 table1 表的字符集为 latin1,列没有设置默认字符集,那么数据库就会把存入的数据做 utf8 转 latin1 的操作。
  4. 图中 6 的位置表示,当服务器发送结果集或返回错误信息到客户端之前,使用 character_set_results 变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据, 例如列值和结果元数据(如列名)。
    沿用上面的例子,假如 character_set_results 为 utf8,则从 table1 抽取出来的数据,会做编码转换,从 latin1 转换成 utf8。
  5. 图中 6 箭头指向 connection 位置,表示 character_set_results 字符集转 character_set_connection 的操作。后续过程就跟刚才的请求过程是一致的。

设置技巧

编码的问题是很恼人的一件事情,但只要弄懂,并多折腾实践下还是蛮好理解的。而有些技巧我们可以在平时的开发过程中使用到的:

  1. 设置 Mysql 配置文件
    这个在讲配置的时候有讲到具体的操作方法,可以回看上面的文字。
  2. 在创建数据库的时候使用 utf8,其他的表和列自然就可以默认继承下来了。 也可以尝试把整个 database 的字符集改成 utf8,但在做这个操作前要记得备份,并把其他非 utf8 的表格做编码转换工作。比较危险,不建议马上尝试 =.=….. 不过,可以参考下这篇教程 (http://www.bluebox.net/news/2009/07/mysql_encoding)
  3. 如果你是用 php 的,可以在数据链接成功后,接着写个查询语句:// 假设 $this 指向你数据类的实例
    $this->link=mysql_connect($host,$usr,$password);
    mysql_query(“SET NAMES UTF8”,$this->link);

但笔者不建议你这样做,原因如下:

这个方法已经被 PHP 官方声明放弃使用,而改用 mysql_set_charset (string $charset [, resource $link_identifier] ) 。大概原因是因为 mysql_set_charset 除了起到改变那三个变量的值外,还修改了其他一些关键的部分,关于关键部分的具体的信息大家可以看看这篇文章
// 假设 $this 指向你数据类的实例
$this->link=mysql_connect($host,$usr,$password);
mysql_set_charset(“utf8”,$this->link);

回看本文一开提出的问题

那么,当我们在 Mysql 运行时,在 mysql 命令下输入 以下命令的时候,为什么就能解决乱码问题呢?

//假设$this指向你数据类的实例
$this->link=mysql_connect($host,$usr,$password);
mysql_set_charset(“utf8”,$this->link);

原来,在我的数据库中,表的默认字符集编码是 utf8,而 connection 是 latin1 的,results 是 latin1,client 是 utf8。
从数据库中拿出 utf8 编码的数据之后,转成 latin1 编码的 results;再经过 connection 后,由于是相同的编码 latin1, 所以不用转换;最后到达 client 的时候,再转成 utf8 编码格式。

大致过程:utf8->latin1->latin1->utf8,最后浏览器自动判断编码。而由于 latin1 对中文支持存在问题(具体原因待查),导致了中文字符出现乱码。所以,解决方案就是,把 connection、results 的编码改成 utf8,最好 4 个地方都统一起来。

总结

总的来说,编码问题无非就两个地方,一个是你怎么存储数据,一个是你怎么传输数据,在上面的示意图中,只要把各个环节的编码集统一起来,基本就不会有问题的了。

在这个过程中,一定要多查手册,google 各种你能找到的资源,然后动手实践去判断一些事情,最好能再深入挖掘下,最后写篇文章总结下,mark 下进度。

TODO

  1. 了解 PHP 的源代码,底层的 C 实现,知道 PHP 底层是怎么调用 mysql 的接口实现一系列的交互操作的。
  2. 编译 Mysql 的源码,也是底层的 C 实现,从实现的角度来探讨 character_set% 等导致的乱码问题,以及可能的解决方案。
  3. 熟悉 Mysql 的手册,特别是在命令行下命令,以及相关的优化技术,常见问题如应对字符编码乱码,安全问题等。
  4. 继续完善这篇文章,或单独开一个系列、栏目,如校对的概念、latin1 编码与 utf8 的区别、如何把非 utf8 转换成 utf8 格式、怎么转移大批量的数据最划算等等话题…….
  5. 把页面的样式再调整下,太难看了…..

参考资料

  1. MYSQL 编码问题的汇总
  2. Specifying Character Sets and Collation
  3. server-system-variables
  4. 4.CSDN 关于字符集编码底层实现的讨论