mysql的charset和collate

mysql

mysql的charset

charset用于给数据库确定使用哪种编码方式进行编码

mysql的collate

collate叫做数据库的校验，就是一种对字符串进行比较的规则

stackoverflow上对charset和collate的解释：

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

mysql中常用的编码和校验方式

utf-8和unicode

unicode是确定字符的编码值，但是具体怎么用多大的空间来存储室没有确定的，在计算机系统中，存储在硬件的使用试用unicode进行编码，读出来进行读写是使用utf-8进行编码的

utf-8和ascii

ascii编码是使用7位来进行编码的，utf-8是小的字符用一字节，多的用到2字节到4字节，当时utf-8用一字节进行编码的时候和ascii一样。

utf-8和latin1

Latin1是ISO-8859-1的别名，顾名思义，1这种编码方式可以编码拉丁字母，ISO-8859-1收录的字符除ASCII收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。如果用latin1来编码非拉丁字母的中文日文等就会出现乱码。latin1用一字节来存储一个字符。

mysql实践中的问题

问题阐述
mysql database的编码和校验方式会影响mysql连接器(比如我一直是用mysql.connector)在查询varchar的时候，将结果返回为bytearray的形式还是是正常的string类型。charset为utf-8时，varchar字段的查询结果返回类型为bytearray，虽然这个问题可以在直接使用mysql.conector的时候自己decode一下来解决，但是当mysql.connector配合sqlalchemy这样的orm一起使用的时候，问题就尤为明显，而且难以解决。在sqlalchemy配合mysql.connector中，query函数中传入varchar类型的字段作为查询的条件的时候，报错：typeerror：bytearray is not hashalbe。显示返回的查询类型是bytearray，并且这是不可哈希的类型，所以sqlalchemy的查询报错。

问题原因
这应该是mysql.connector 2版本自己的设计或者一个bug，因为详细可以看Connector/Python 2.0.0官方文档说明

这里说明，connector版本1返回值的类型是正常的，python2返回时字符串，python3是bytes，但是到了connector版本2，无论哪个版本的python返回值一律都是bytearray

解决方案
据我观察

• charset为utf-8，返回值类型为bytearray
• charset为latin1，返回值类型为string
• charset为gbk，返回值类型为string，而gbk可以对中文编码

所以得出上面所说的，数据库的charset可以影响connector的返回值类型，这很迷，谷歌搜到的解释很多事stackoverflow上的，对于这个问题也是众说纷纭，所以也不知道这算是一个bug还是connector本身的一个设计。上面这样解决也要考虑当存储非拉丁字符时会出现乱码的情况。

在创建database时确定它的charset和collate

CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;

latin1_swedish_ci意为瑞典的一个拉丁字符比较方式，ci意为不计较大小写。

通过上面设置database的charset和collate的方式就可以让connector的varchar返回类型为string，sqlalchemy的query不报错了。

总结

在mysql.connector+sqlalchemy对mysql进行含有中文字段的row进行增删查找，可以设置数据库的charset为gbk。这样mysql.connector查询的返回值为string，sqlalchemy的query不会报错。

create database test2 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; 

测试代码

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from sqlalchemy import Column, String, Integer, create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
# 创建对象的基类:
Base = declarative_base()
# 定义User对象:
class User(Base):
    __tablename__ = 'user'
    id = Column(String(20), primary_key=True)
    name = Column(String(20))
# 初始化数据库连接:
engine = create_engine('mysql+mysqlconnector://root:Gzm20125@localhost:3306/test')
# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)
#Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# 创建session对象:
session = DBSession()
# 创建新User对象:
new_user = User(id="5", name='Bob')
# 添加到session:
session.add(new_user)
# 提交即保存到数据库:
session.commit()
# 关闭session:
session.close()
# 创建Session:
session = DBSession()
# 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
user = session.query(User).filter_by(id = "5").one()
# 打印类型和对象的name属性:
print('type:', type(user))
print('name:', user.name)
# 关闭Session:
session.close()