mysql collate_mysql的charset和collate

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和collateCREATE 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 -*-

fromsqlalchemyimportColumn,String,Integer,create_engine

fromsqlalchemy.ormimportsessionmaker

fromsqlalchemy.ext.declarativeimportdeclarative_base

# 创建对象的基类:

Base=declarative_base()

# 定义User对象:

classUser(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()