MySQL的使用注意事项_show variables like 'autocommit' 无效语句

写在前面

常见概念：数据库、表、特征、记录、主键、外键、一对一、一对多、多对多
SQL概念：Structured Query Language- - -结构化查询语言
数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句
数据操作语言(DML)，例如：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句
数据查询语言(DQL)，例如：SELECT语句
数据控制语言(DCL)，例如： COMMIT、ROLLBACK、GRANT、REVOKE等语句

常见命令

SHOW DATABASES;
SHOW TABLES;
DROP TABLE table_name ;
use databaseone;
SHOW COLUMNS FROM dataku
select * from dataku
select @@autocommit;
show variables like ‘%autocommit%’;
show variables like ‘%timeout%’;
FLUSH PRIVILEGES;刷新权限
exit

修改数据库的连接时间

SHOW GLOBAL VARIABLES LIKE ‘%timeout%’
Linux系统下，配置文件为路径/etc/my.cnf
[mysqld]
wait_timeout=10000
interactive_timeout=10000
保存退出，重启MySQL服务即可

Fetchone && Fetchall用法与区别

fetchone() ：返回单个的元组，也就是一条记录(row)，如果没有结果 则返回 None
fetchall() ：返回多个元组，即返回多个记录(rows),如果没有结果 则返回 ()
需要注明：在MySQL中是NULL，而在Python中则是None
fetchone()用法：

cur.execute("select host,user,password from user where user='%s'" %acc)
jilu = cur.fetchone() 
##此时 通过 jilu[0],jilu[1],jilu[2]
##可以依次访问host,user,password（每一条记录中的特征）
1
2
3
4

fetchall()用法：

cur.execute("select * from user")
jilu = cur.fetchall() 
##此时 通过 jilu[0],jilu[1],jilu[2]，每一个是一条记录，而非特征
1
2
3

如果select本身取的时候有多条数据时：
cursor.fetchone()：将只取最上面的第一条结果，返回单个元组如(‘id’,‘title’)，然后多次使用cursor.fetchone()，依次取得下一条结果，直到为空
cursor.fetchall() ：将返回所有结果，返回二维元组，如((‘id’,‘title’),(‘id’,‘title’))
如果select本身取的时候只有一条数据时：
cursor.fetchone()：将只返回一条结果，返回单个元组如(‘id’,‘title’)
cursor.fetchall() ：也将返回所有结果，返回二维元组，如((‘id’,‘title’)，(‘id’,‘title’))

Commit 和 Rollback
Commit：提交事务
查看事务状态：select @@autocommit; show variables like ‘%autocommit%’;
1或者ON表示自动提交；0或者OFF表示手动提交：需要commit命令提交事务
Rollback：退回事务，不提交
推荐博文https://blog.csdn.net/weixin_43639512/article/details/84304672

数据库用户管理和权限管理

管理公式
配置用户本地 IP 访问 localhost, 127.0.0.1
create user ‘username’@‘localhost’ identified by ‘password’;
配置用户外网 IP 访问
create user ‘username’@’%’ identified by ‘password’;
grant all privileges on . to ‘username’@’%’ identified by ‘password’;
grant SELECT,DELETE,UPDATE on . to ‘username’@’%’ identified by ‘password’;
原文链接：https://blog.csdn.net/wsysunny/article/details/82970048

【设置参考】
1.允许本地访问的用户（127.0.0.1）
create user zhrt@localhost identified by ‘123456’;
2.允许外网IP访问的用户
create user ‘zhrt’@’%’ identified by ‘123456’;
3.用户分配权限
授予用户在本地服务器对该数据库的全部权限
grant all privileges on dbname.* to zhrt@localhost identified by ‘123456’;
授予用户通过外网IP对于该数据库的全部权限
grant all privileges on dbname.* to ‘zhrt’@’%’ identified by ‘123456’;
grant select,insert,update,delete,create,drop on weizany.tb to www@115.57.134.46 identified by ‘123456’;
给来自115.57.134.46的用户www分配可对数据库weizany的tb表进行
select,insert,update,delete,create,drop等操作的权限，并设定口令为123456

grant all privileges on weizany.* to www@115.57.134.46 identified by ‘123456’
给来自115.57.134.46的用户www分配可对数据库weizany所有表进行所有操作的权限，并设定口令为123456

grant all privileges on . to www@115.57.134.46 identified by ‘123456′;
给来自115.57.134.46的用户www分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123456。

grant all privileges on . to www@localhost identified by ‘123456′;
给本机用户www分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123456。

推荐博文
https://blog.csdn.net/yhjahjj1314/article/details/79571882
https://blog.csdn.net/wsysunny/article/details/82970048?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task

SQL防止注入
SQL注入就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。对于很多网站都有用户提交表单的端口，提交的数据插入MySQL数据库中，就有可能发生SQL注入安全问题

针对SQL注入安全问题的预防，需时刻认定用户输入的数据是不安全的，并对用户输入的数据进行过滤处理，对不同的字段进行条件限制，符合条件的可以写入数据库，不符合条件的进行数据过滤处理，防止SQL注入，需要注意以下几点：
1.永远不要信任用户的输入，对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换
（一些特殊符号输入到post的参数后，会导致sql的语句执行发生变化）
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。利用pymsql中的参数执行模式，可以一定程度上减少风险

ql_findi = 'SELECT ID_card FROM dataku4 WHERE ID_card=%s'
cursor.execute(sql_findi,(ID_card))
1
2

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等

基于WEB框架使用的数据库
flask_sqlalchemy是一个可以支持多个数据库的后端，提供ORM和基本的SQL操作
因此操作对应的数据库的时候还需要安装对应的库支持包
由于flask对sqlalchemy进行了二次封装，所以不能直接使用sqlalchemy，而需要先安装flask_sqlalchemy

1、连接
Python-DBUtils 使用mysql数据库连接池提升效率
mysql长时间连接未操作会自动断开，conf设置了相应的wait时间
2个问题导致连接池的使用诞生
所有线程用1个连接，连接断开则程序出现问题，稳定性差
每一个sql操作的时候，都单独建立一个SQL连接，执行完成后就断开
导致频繁连接，系统开销较大
Application------Connecttion 1 Connecttion x*(pool)----------DB

1.在程序创建连接的时候，可以从一个空闲的连接中获取，不需要重新初始化连接，提升获取连接的速度
2.关闭连接的时候，把连接放回连接池，而不是真正的关闭，所以可以减少频繁地打开和关闭连接
1
2

2、建数据库和表
一对多（建立一个外键）、多对多（建立两张表的中间关系表）的关系

class User(Base):
 __tablename__ = 'user'
 id = Column(String(20), primary_key=True)
 name = Column(String(20))
 #一对多:
 books = relationship('Book')

class Book(Base):
   __tablename__ = 'book'
   id = Column(String(20), primary_key=True)
   name = Column(String(20))
 # “多”的一方的book表是通过外键关联到user表的:
   user_id = Column(String(20), ForeignKey('user.id'))
（外键并不会被创建）
1
2
3
4
5
6
7
8
9
10
11
12
13
14

当我们查询一个User对象时，该对象的books属性将返回一个包含若干个Book对象的list

3、常见操作
增删改查，尤其需要注意查询语句的条件

最后是在shell命令行中直接执行SQL脚本文件：方法参照网站具体，文章较多，不在此赘述。

4、简单实例

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from DBUtils.PersistentDB import PersistentDB
from warnings import filterwarnings
import pymysql
filterwarnings("ignore",category=pymysql.Warning)

#数据库连接池DBUTILS 进行数据库的连接
POOL = PersistentDB(
    creator=pymysql, 
    maxusage=None,  
    setsession=[],
    ping=0,
    closeable=False,
    threadlocal=None,  
    host='127.0.0.1',
    port=3306,
    user='root',
    password='××××',
    database='databaseone',
    charset='utf8')
 
def create_table():
    """
        函数名： create_table()
        描述： 创建数据库名和表名
        输入：无
        输出：flag 标志位  标志位为0 表示失败；标志位为 1 表示成功.
    """
    try:
        conn = pymysql.connect(
        host='localhost',
        user='××××',password='××××',port=3306,
        charset='utf8')
        cursor = conn.cursor()
        cursor.execute('CREATE DATABASE IF NOT EXISTS databaseone DEFAULT CHARSET utf8')
        cursor.execute('USE databaseone')
        cursor.execute('''CREATE TABLE IF NOT EXISTS dataku1 (
        id INT auto_increment PRIMARY KEY NOT NULL,
        name CHAR(28) NOT NULL,
        ID_card VARCHAR(18) NOT NULL UNIQUE,
        user_number SMALLINT NOT NULL UNIQUE,
        eigenvalue1 BLOB NOT NULL,
        eigenvalue2 BLOB NOT NULL,
        eigenvalue3 BLOB NOT NULL,
        distance INT NOT NULL
        )ENGINE=innodb DEFAULT CHARSET=utf8''')
        cursor.execute("SHOW TABLES LIKE 'dataku' ")
        table = cursor.fetchone()
        if table == None:
            cursor.close()
            conn.close()
            return 0
        else:
            cursor.close()
            conn.close()
            return 1
    except BaseException as ee:
            cursor.close()
            conn.close()
            print(ee)
    else:
        pass

app = Flask(__name__)
 
#设置连接数据库的URL
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://username:passwd@127.0.0.1:3306/databaseone?charset=utf8'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
#查询时会显示原始SQL语句
app.config['SQLALCHEMY_ECHO'] = False
db = SQLAlchemy(app)
 
class User(db.Model):
    """ 定义了三个字段， 数据库表名为model名小写
    """
    __tablename__ = 'User'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, email):
        self.username = username
        self.email = email

    def __repr__(self):
        return '<User %r>' % self.username

    def save(self):
        db.session.add(self)
        db.session.commit()

if __name__ == '__main__':
    db.drop_all() #只会全部删除由db.modle定义的表，不会影响库中的其他表
    # create_table()
    db.create_all()
    u1 = User(username='admin1', email='admin1@example.com') # 创建实例
    u2 = User(username='wangaan', email='wangaan@example.com') # 创建实例
    db.session.add_all([u1, u2])
    # db.session.add(u1)                                    # 添加session
    db.session.commit()                                   # 提交查询
    users = User.query.filter_by(username='wangaan').all()                            # 查询
    print(users[0].email)
    u2.email = 'wanghanha@qq.com'
    db.session.add(u2)
    # db.session.delete(u1)
    db.session.commit()
    users = User.query.filter(User.username.endswith('n')).all()
    print(users[0].email)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109

5、MySQL的分布式部署
技术性上：参考文章
MySQL数据库提供主从复制的功能，可以实现数据的多处自动备份，数据的安全性、读写分离，提高数据库的读写性能、负载均衡，提高数据库的高可用和容错性
实现步骤：
1、将MySQL的某一台主机（Master）的数据复制到其它主机（Slave）上，并重新执行一遍来实现的。复制过程中，一台服务器充当Master，而一个或多个其它服务器充当Slave
2、MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个【I/O线程】与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个 【SQL线程】会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制
3、三个步骤：
Master将改变记录到二进制日志中
Slave将Master的二进制日志拷贝到它的中继日志(Relay_log)
Slave重做中继日志中的事件，将改变反映它自己的数据

原理性上：参考文献
分布式DB的关键技术：分库、分表、M-S、集群、负载均衡

• 水平切分DB：有效降低了单台机器的负载，也减小了宕机的可能性（考虑分库后的路由规则选择和制定、后期拓展）
• 集群方案：解决DB宕机带来的单点DB不能访问问题，主要是物理层面上分开数据库内容
• 读写分离策略：极大限度提高了应用中读数据的速度和并发量（一台主数据库服务器作为master和写操作、其余的slaver数据库复制主数据库的内容且只作为读数据库功能）

数据切分（考虑迁移难度、性能分配、拓展性、读写性能和可靠性安全性）：

1. 物理上的切分，通过一系列的切分规则将数据库的数据分布到不同的DB数据库，利用设定的路由规则来访问特定的数据库，利用N台数据库来降低访问的压力
2. 软件上的切分，将数据通过切分规则，分成数据库中的不同表
3. 分开方法：主键分割（user_id为区分，1～1000的对应DB1，1001～2000的对应DB2）、hash取模分（比如应用中需要将一个数据库切分成4个数据库的话，我们就用4这个数字对user_id的hash值进行取模运算，也就是user_id%4，这样的话每次运算就有四种可能：结果为1的时候对应DB1）、在认证库中保存数据库配置（利用额外的数据库实现user_id和对应分开数据库的映射关系）

6、乐观锁和悲观锁
背景：解决并发过程中可能产生的冲突

1. 丢失更新，一个事务的更新覆盖了其它事务的更新结果，A将1改为2，B将2改为1
2. 脏读，当一个事务读取其它完成一半事务的记录时，A\B均读为1、A将1改为2后，B在改的过程中读了数据，并未及时读到更新数据

概念：悲观锁，假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁，假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性，乐观锁不能解决脏读的问题

【乐观锁】推荐1推荐2
常见的应用场景：

• 电商网站库存，当多用户并发修改同一条记录时，可定是后提交的用户将覆盖前者提交的结果，这个时候，可以使用加乐观锁的机制去解决高并发的问题
• Reids 的并发竞争问题解决方案，使用乐观锁方式进行解决，成本低，非阻塞，性能较高

解决方法：
1、版本号机制，一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加1。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功
2、字段类型使用时间戳，和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突
3、CAS算法—即compare and swap（比较与交换），是一种有名的无锁算法，无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（该算法的ABA问题，自旋循环时间可能比较长），需要读写的内存值 V、进行比较的值 A、拟写入的新值 B
当且仅当 V 的值等于A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作），一般情况下是一个自旋操作，即不断的重试

7、MySQL的innodb如何定位锁问题，mysql如何减少主从复制延迟？
mysql的innodb如何定位锁问题:
在使用 show engine innodb status检查引擎状态时，发现了死锁问题
information_schema 库中增加了三个关于锁的表（MEMORY引擎）
innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

8、MySQL如何减少主从复制延迟？
如果延迟比较大，就先确认以下几个因素：
从库硬件比主库差，导致复制延迟
主从复制单线程，如果主库写并发太大，来不及传送到从库就会导致延迟。
更高版本的mysql可以支持多线程复制
慢SQL语句过多
网络延迟
master负载，主库读写压力大，导致复制延迟，架构的前端要加buffer及缓存层
slave负载，一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作。另外， 2个可以减少延迟的参数:–slave-net-timeout=seconds 单位为秒 默认设置为 3600秒
参数含义：当slave从主数据库读取log数据失败后，等待多久重新建立连接并获取数据
–master-connect-retry=seconds 单位为秒 默认设置为 60秒
参数含义：当重新建立主从连接时，如果连接建立失败，间隔多久后重试
通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟

9、MySQL数据库主从同步延迟解决方案
最简单的减少slave同步延时的方案就是在架构上做优化，尽量让主库的DDL快速执行，还有就是主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit= 1 之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave