ci配置mysql和mssql_MySQL和SqlAlchemy配置指北

近期做了一个项目+库存的管理应用，开发时偷懒用的SQLite，上线后再调整时有时候要直接进数据库改数据，开始想念navicat的好处，动了上MySQL的念头，折腾一番后把一些安装要点写在这里。

安装

安装最新版本的MySQL在Ubuntu上是十分容易的。只要执行

$ sudo apt-get install mysql-server mysql-client

安装过程中会要求设置用户的root密码。

其中, mysql-server相当于mysql数据库的核心系统，mysql-client则提供交互管理的组件，比如`mysql`命令进入的命令行管理程序，就是mysql-client提供的。

配置

值得一提的是我安装好的mysql版本(5.7.20)，在/etc/mysql/my.cnf文件的内容为:

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mysql.conf.d/

也就是说，按这个版本的实践，最好去这两个目录里修改对应的配置文件。

为了能够远程直接访问数据库，需要更改数据库的bind-address。

本版本中，是前往` /etc/mysql/mysql.conf.d/`目录，修改mysqld.cnf文件，如下：

[mysqld]

# log文件配置

bind-address = 0.0.0.0

重启数据库的方式:

$ sudo /etc/init.d/mysql restart

账户和权限

安装完的Mysql提供了一个只能从本地访问的root账户。通过root账户登录数据库，可以查看`mysql database`中的数据。查看mysql中用户列表的方法:

mysql > use mysql; # 进入mysql数据库

mysql > select host, user from mysql.user; # 查看用户列表

直接能远程登录root账户显然是不安全的，目前我采用的办法是用root账户创建项目的数据库和开发数据库，然后创建使用账户，授权给这个账户对于这两个数据库的所有权限。

命令如下:

mysql> create database project_name;

mysql> create database project_name_dev;

mysql> create user ‘username’@’%’ identified by ‘password’;

mysql> grant all on project_name.* to ‘username’@’%’;

mysql> grant all on project_name_dev.* to ‘username’@’%’;

文字编码问题

在实际使用MySQL时，我遇到第一个头痛的问题是存入的中文变成了乱码，或者报错。

首先是乱码问题，默认安装完的MySQL(我的版本是5.7.20)采用的是latin1的数据库字符集，如果要更改以后创建的数据库的字符集可以去/etc目录修改配置文件。

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和字段级。

服务器字符集和校对规则可以在MySQL服务启动的时候确定。

服务器级的默认字符集和校对规则可以通过my.cnf中设置:

[mysqld]

character-set-server = utf8mb4

collation_server = utf8mb4_unicode_ci

连接字符集和校对规则

在配置文件/etc/mysql/conf.d/mysql.cnf修改默认编码：

[mysql]

default-character-set =utf8mb4

[client]

default-character-set =utf8mb4

[mysqld]

# init_connect: Astring to be executed by the server for each client that connects. The string consists of one or moreSQL statements, separated by semicolon characters.

init_connect= 'SET NAMES utf8mb4'

关于这个utf8mb4多出来的mb4，mb4就是most bytes 4的意思。MySQL在5.5.3之后增加了这个utf8mb4的编码，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。我一开始采用utf8编码时，导入数据就会遇到“超过3个字节”的错误，采用utf8mb4能解决这个问题。有人说utf8mb4的会占用更多内存是不对的，因为utf-8本来就是变长编码，是mysql一开始对utf-8的支持就不正确，所有字符都需要四个bytes来存储就是UTF-32了。

所有涉及的配置变量的官方文档见链接。按本文设置完在mysql命令行中结果如下所示：

$ mysql> show variables like '%char%';+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

如何在SqlAlchemy中指定显式表的引擎和字符集

class User(Base):

__table_args__ = {

'mysql_engine': 'InnoDB',

'mysql_charset': 'utf8mb4'

}

也可以在基类上直接修改:

class Base (Base):

__abstract__ = True

__table_args__ = { # 可以省掉子类的 __table_args__ 了

'mysql_engine': 'InnoDB',

'mysql_charset': 'utf8mb4'

}

使用基类来创建所有表都需要的字段的方法:

链接:

ORM中计数器的”atomic increment”

具体而言，假设我们有个`post`表，对应有一个叫Post的class:

class Post:

__tablename__ = ‘post’

id = db.Column(db.Integer, primary_key=True)

content = db.Column(db.Text)

uv = db.Column(db.Integer)

pv = db.Column(db.Integer)

然后我们在视图函数中每次访问执行以下语句是会有问题的:

# 错误示范1

p.uv = p.uv + 1

# 错误示范2

p.uv += 1

我们暂时不考虑缓存设施，先让视图函数直接访问操作数据库。

假设一篇id为2的文章在下午3点的uv是5。然后恰好有两个人在3点00分01秒同时点击这篇文章，可能就会出现竞争问题。线程一接待了客户1，从数据库读到了uv = 5。然后线程被调度挂起了，线程2开始接待客户2，再次从数据库中读到了uv = 5。然后两个线程在python运行环境中计算出5 + 1 => 6，先后执行SQL语句

UPDATE `post` SET `pv` = 6 WHERE `id` = 2

语句被执行了两遍，uv没有被加到7，GG。

正确的做法应该是视图函数要去传入SQL语句

UPDATE `post` SET (`pv` = `pv` + 1) WHERE `id` = 2

那么SqlAlchemy中如何做呢？很简单：

p.uv = Post.uv + 1

如此自然，SqlAlchemy棒棒的。