Mysql

[chengtm]

事务隔离级别

查看当前会话的级别：select @@tx_isolation;
设置mysql的隔离级别：set session transaction isolation level 设置事务隔离级别

• read uncommitted，也称做脏读，读取的是别人可能不会提交的数据，级别最低；

• read committed， 大多数数据库的默认级别，会导致不可重复读，即一个session中第一次读的数据与第二次读取的数据可能不同；

• repeatable read，mysql的默认级别，一个session中多次读取数据一致，但是会造成插入ID时重复问题，这种现象称为幻读。但是MySQL已解决该现象；

• serializable，串行化，一次只能一个事务，其他会话的写操作会被挂起。

存储表情

https://juejin.im/post/5ce1f3955188250fad23d2db

ALTER TABLE comment MODIFY content text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '内容';

centos下安装mysql

下载mysql的repo源:wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
下载mysql-community-release-el7-5.noarch.rpm包: sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm
安装：sudo yum install mysql-server mysql mysql-devel mysql-libs

远程访问MySQL

mysql> grant all privileges on *.* to root@"%" identified by "password" with grant option; 
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

mysql8.0版本如果按以上命令会出现错误：
。。。for the right syntax to use near 'IDENTIFIED BY。。。，应该跟版本升级，命令废弃有关。以下命令亲测有效：

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你自己的密码';
mysql> flush privileges;

ps: 记得查看3306端口是否开放，默认是开放的；再查看防火墙iptables -L是否对MySQL设置了防火墙。

Python 'tuple' cannot be converted to a MySQL type

jy_channel = db.session \
        .query('source_mobile') \
        .from_statement(text('select source_mobile from jy_channel'
                             ' where channel_department in :channel_department')) \
        .params(channel_department=(1, 4)) \
        .all()

解决方案：
将in的范围写成select子查询：

 jy_channel = db.session \
        .query('source_mobile') \
        .from_statement(text('select source_mobile from jy_channel'
                             ' where channel_department in (select 1 union select 4)')) \
        .all()

在in的范围是枚举级别时，可以用or查询：

jy_channel = db.session \
        .query('source_mobile') \
        .from_statement(text('select source_mobile from jy_channel'
                             ' where (channel_department = :channel_department1 '
                             '  or channel_department = :channel_department2)')) \
        .params(channel_department1=1, channel_department2=4) \
        .all()

select中的结果参与多次计算

1. 通过@指定参数，再通过参数取值
   select @paying_user_number := ( select count(*) from =xxx) as '付费人数',@paying_rate := @paying_user_number / stats.total as '付费率' from tttt
2. 通过子查询
   select subtable.count as '付费人数', subtable.count/stats.total as '付费率' from (select ( select count(*) from =xxx) as count from tttt)subtable