正常情况下，死锁发生时，权重最小的连接将被kill并回滚。但是为了找出语句来优化，启用可启用死锁将死锁信息记录下来。#step 1：窗口一mysql> start transaction;mysql> update aa set name='aaa' where id = 1; #step 2：窗口二mysql> start transaction;mysql> update bb set name='bbb' where id = 1; #step 3：窗口一mysql> update bb set name='bbb';#step 4：窗口三#是否自动提交mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+ #查看当前连接mysql> show processlist;mysql> show full processlist;mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;+----+------+-----------+------+---------+------+-------+------------------+| Id | User | Host  | db | Command | Time | State | Info    |+----+------+-----------+------+---------+------+-------+------------------+| 4 | root | localhost | test | Sleep | 244 |  | NULL    || 5 | root | localhost | test | Sleep | 111 |  | NULL    || 6 | root | localhost | NULL | Query | 0 | init | show processlist |+----+------+-----------+------+---------+------+-------+------------------+ #查看当前正在被锁的事务（锁请求超时后则查不到）mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+| lock_id   | lock_trx_id | lock_mode | lock_type | lock_table | lock_index  | lock_space | lock_page | lock_rec | lock_data  |+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+| 130718495:65:3:4 | 130718495 | X   | RECORD | `test`.`bb` | GEN_CLUST_INDEX |   65 |   3 |  4 | 0x000000000300 || 130718496:65:3:4 | 130718496 | X   | RECORD | `test`.`bb` | GEN_CLUST_INDEX |   65 |   3 |  4 | 0x000000000300 |+------------------+-------------+-----------+-----------+-------------+-----------------+------------+-----------+----------+----------------+ #查看当前等待锁的事务（锁请求超时后则查不到）mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; +-------------------+-------------------+-----------------+------------------+| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |+-------------------+-------------------+-----------------+------------------+| 130718499   | 130718499:65:3:4 | 130718500  | 130718500:65:3:4 |+-------------------+-------------------+-----------------+------------------+ #查看当前未提交的事务（如果死锁等待超时,事务可能还没有关闭）mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;+--------------------------------------------------------------------------------------------------------+| trx_id | trx_state | trx_started   | trx_requested_lock_id | trx_wait_started | trx_weight |+-----------+-----------+---------------------+-----------------------+---------------------+------------+| 130718500 | RUNNING | 2018-03-12 09:28:10 | NULL     | NULL    |   3 || 130718499 | LOCK WAIT | 2018-03-12 09:27:59 | 130718499:65:3:4  | 2018-03-12 09:32:48 |   5 |==========================================================================================================| trx_mysql_thread_id | trx_query        | trx_operation_state | trx_tables_in_use |+---------------------+---------------------------------------+---------------------+-------------------+|     4 | NULL         | NULL    |     0 ||     5 | update bb set name='bbb'    | starting index read |     1 |=========================================================================================================| trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified |+-------------------+------------------+-----------------------+-----------------+-------------------+|     0 |    2 |     360 |    3 |     1 ||     1 |    4 |     1184 |    4 |     1 |===========================================================================================================================| trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error |+-------------------------+---------------------+-------------------+------------------------+----------------------------+|      0 | REPEATABLE READ  |     1 |      1 | NULL      ||      0 | REPEATABLE READ  |     1 |      1 | NULL      |===========================================================================================================================| trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |+---------------------------+---------------------------+------------------+----------------------------+|       0 |      10000 |    0 |       0 ||       0 |      10000 |    0 |       0 |+---------------------------+---------------------------+------------------+----------------------------+ #查看正在被访问的表mysql> show OPEN TABLES where In_use > 0;+----------+-------+--------+-------------+| Database | Table | In_use | Name_locked |+----------+-------+--------+-------------+| test  | bb |  1 |   0 |+----------+-------+--------+-------------+#step 3：窗口一 （若第三步中锁请求太久，则出现锁超时而终止执行）mysql> update bb set name='bbb';ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #"窗口一" 锁请求超时前，执行第五步，使死锁产生，则该连接 "窗口二" 执行终止，"窗口一" 顺利执行#step 5：窗口二mysql> update aa set name='aa';ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction查看最近一个死锁情况#查看最近一个死锁情况mysql> SHOW ENGINE INNODB STATUS\G ;...............------------------------LATEST DETECTED DEADLOCK------------------------2018-03-12 11:01:06 7ffb4993a700 #发生时间*** (1) TRANSACTION: #事务1TRANSACTION 130718515, ACTIVE 19 sec starting index readmysql tables in use 1, locked 1 #正被访问的表LOCK WAIT 4 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1 #影响行数MySQL thread id 5, OS thread handle 0x7ffb498f8700, query id 205 localhost root updating #线程/连接host/用户update bb set name='bb' #请求语句*** (1) WAITING FOR THIS LOCK TO BE GRANTED: #等待以下资源 （锁定位置及锁模式）RECORD LOCKS space id 65 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`bb` trx id 130718515 lock_mode X waitingRecord lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000300; asc  ;; 1: len 6; hex 000007ca9b34; asc  4;; 2: len 7; hex 1f000002092075; asc  u;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6262; asc bb;; *** (2) TRANSACTION: #事务2TRANSACTION 130718516, ACTIVE 14 sec starting index readmysql tables in use 1, locked 14 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 1MySQL thread id 4, OS thread handle 0x7ffb4993a700, query id 206 localhost root updatingupdate aa set name='aa' #请求语句*** (2) HOLDS THE LOCK(S): #持有锁资源RECORD LOCKS space id 65 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `test`.`bb` trx id 130718516 lock_mode XRecord lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000301; asc  ;; 1: len 6; hex 000007ca9b17; asc  ;; 2: len 7; hex 9000000144011e; asc  D ;; 3: len 4; hex 80000002; asc  ;; 4: len 2; hex 6262; asc bb;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000300; asc  ;; 1: len 6; hex 000007ca9b34; asc  4;; 2: len 7; hex 1f000002092075; asc  u;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6262; asc bb;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 64 page no 3 n bits 80 index `GEN_CLUST_INDEX` of table `test`.`aa` trx id 130718516 lock_mode X waitingRecord lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000200; asc  ;; 1: len 6; hex 000007ca9b33; asc  3;; 2: len 7; hex 1e000001d53057; asc  0W;; 3: len 4; hex 80000001; asc  ;; 4: len 2; hex 6161; asc aa;; *** WE ROLL BACK TRANSACTION (2)...............#死锁记录只记录最近一个死锁信息，若要将每个死锁信息都保存到错误日志，启用以下参数：mysql> show variables like 'innodb_print_all_deadlocks';+----------------------------+-------+| Variable_name    | Value |+----------------------------+-------+| innodb_print_all_deadlocks | OFF |+----------------------------+-------+ #上面 【step 3：窗口一】若一直请求不到资源，默认50秒则出现锁等待超时。mysql> show variables like 'innodb_lock_wait_timeout'; +--------------------------+-------+| Variable_name   | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50 |+--------------------------+-------+ ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction #设置全局变量 锁等待超时为60秒（新的连接生效）#mysql> set session innodb_lock_wait_timeout=50; mysql> set global innodb_lock_wait_timeout=60; #上面测试中，当事务中的某个语句超时只回滚该语句，事务的完整性属于被破坏了。为了回滚这个事务，启用以下参数：mysql> show variables like 'innodb_rollback_on_timeout';+----------------------------+-------+| Variable_name    | Value |+----------------------------+-------+| innodb_rollback_on_timeout | OFF |+----------------------------+-------+最终参数设置如下：(重启服务重新连接测试)[mysqld]log-error =/var/log/mysqld3306.loginnodb_lock_wait_timeout=60  #锁请求超时时间(秒)innodb_rollback_on_timeout = 1 #事务中某个语句锁请求超时将回滚真个事务innodb_print_all_deadlocks = 1 #死锁都保存到错误日志

我们经常所高并发，高可用。就是从质和量来评估，任何事物都可以从这两个角度来分析。在Mysql数据库中，事务就是用来保证质的，MVCC就是用来保证量的。事务我们使用事务来保证每一条SQL语句的结果执行符合我们的预期。我们说事务必须具备ACID特性。ACID中的三者：原子性、一致性和持久性其实描述的都差不多，保证SQL执行结果的可靠性。而隔离性就比较复杂了，隔离性描述的是在并发场景下数据库的表现，但并发量并不是固定的，而不同的业务可能有不同的需求，为了使数据库能适应不同的并发场景，所以伟大的人们又定义了四种隔离级别：Read Uncommited，Read Committed (RC)，Repeatable Read (RR)，Serializable。随着数据库隔离级别的提高，数据的并发能力也有所下降。隔离级别在RC隔离级别下，修改数据会加排它锁，事务结束释放，其他事务不许读，解决脏读问题。（共享锁当场释放）在RR隔离级别下，读数据加共享锁，事务结束释放，其他事务不许修改，解决不可重复读。（共享锁事务结束释放）实际上都把操作串行化了。而Mysql对其进行了优化，一个事务读时其他事务不能写，一个事务写时其他事务不能读？我不这么干照样能解决脏读和不可重复读问题。MVCC出现了。（这也使得问题变得越来越复杂，而不一样的地方也开始出现在RR隔离级别下，碰巧Mysql的默认隔离级别就是RR）MVCCMVCC即多版本并发控制，使用了双版本号来解决数据的隔离问题。（“create”一个版本号，“delete”一个版本号，修改操作拆分为“delete”和“create”）每个事务在开始对每张表增删改查操作时都会生成一个版本号，每个事务只能查到“create”小于本版本号和“delete”大于本版本号的数据。这样，增删查操作就完全可以并发进行了，只有修改操作是一定要排队的。这样，就算没有共享锁也解决了不可重复读问题，因为其他事务修改后，数据的版本号比我大，我不会读到。MVCC在RR隔离级别下的并发引入MVCC之后，看似很美好。然而大家有没有想过两个事务先后对一条数据做更新操作，然后两个事务再读取那条数据，分别读到什么？哈哈，这根本是不可能出现的，因为修改操作是串行的，另一个事务必须先commit本事务才能修改。好，换个问题，两个事务先后对一条数据做+1操作，另一个事务提交后，本事务再+1，再读取那条数据，本事务是读取到+1还是+2的结果？如果读取到+2，那不是破坏了隔离性，读到了其他事务提交的数据么？然而事实确实是这样，其他事务已经提交，本事务也已修改过那条数据了，之后当然要读到+2才行。虽然本来是0，本事务明明只加了1，可读取后却变成2了，有点不适应。确实，在标准的RR隔离级别下，因为操作都是串行的，本事务读取一行数据后，其他事务就不能修改这条数据了，这条数据永远只有本事务在操作，所以严格满足隔离性。但是Mysql的RR增强了读与写的并发，只有当两个事务同时修改一条数据需要串行，其他所有操作都可以并行。所以造成了这种结果，好像出现了不可重复读。但是这种不可重复读实际上是符合我们的直观感受的，在本事务对数据修改后，当然要读取到最新的数据。要对其过程进行分析的话：数据create版本号为0事务1版本号为1，读取数据value=0事务2版本号为2，修改数据value+1=1，原数据delete版本号为2，新数据create版本号更新为2，commit事务1修改数据value+1=2，（由于修改是当前读，永远读取版本号最大的数据，所以读取到value为1）修改后delete版本号为1，新数据create版本号为1本事务读取数据value=2深入分析：其实上面的描述也是有漏洞的，如果有第三个事务版本号为3呢？因为版本号为3，是不是可以直接读取事务1、2未提交的数据？实际上在MVCC中，每个事务还有一个最低可见版本low_limit_id（事务号 >= low_limit_id的记录，对于当前事务都是不可见的），把当前正在执行还没commit的事务给过滤掉了。例如事务3，虽然版本号为3，但是low_limit_id=1，所以事务1和事务2的修改对3都是不可见的。

我的Python环境的Python3Python连接MySQL数据库首先你的Windows系统要安装MySQL 或者你也可以远程只用你的MySQL数据库服务器, 我使用的软件是PyCharm(社区班免费)1——开始前的准备 还是跟SQL server连接数据库一样 首先你要有pymssql这个模块 在python2里面连接MySQL需要MySQLdb这个模块，导入方法这个模块方法就不说了 你可以看我去看我上一篇文章里面有导入方法—链接：https://bbs.huaweicloud.com/forum/thread-109036-1-1.html这里讲一下 cmd里面用pip install pymssql 安装模块出现无法下载问题 在正常的情况下 无法下载模块 可以尝试配置pip.ini这个文件 如何配置？— 首先键盘win+E打开此电脑 如果打不开就手动点开 C:\Users\XXX\  --XXX是指你用户名 在这个目录地下找到pip文件夹 如果没有就新建一个文件夹叫pip然后再里面新建一个文本文档，然后打开文本文档输入 [global]index-url = https://pypi.tuna.tsinghua.edu.cn/simple[install]trusted-host = https://pypi.tuna.tsinghua.edu.cn我的install是清华大学的开源软件镜像站 你也可以在百度上找其他，输入好之后保存 然后把这个文本文档改成 pip 然后把 .txt后缀改成 .ini 如果你的系统没有显示后缀那么你需要打开此电脑然后在此电脑这个窗口的上面然后勾选右面的文件扩展名 正常情况下你就可以看到文件后缀了 (对于Windows10用户的操作)如果你C:\Users\Administrator\AppData\Local\目录下有pip这个文件夹并且里面有pip.ini文件 你可以尝试着更换下载源2——开始连接MySQL好了说完关于安装模块的问题就开始连接MySQL数据库吧，pymysql 导入模块 conn = pymysql.connect(=====) 创建连接对象 hosts后面的是你数据库地址 127.0.0.1可以代表本地的地址,user是连接数据库的用户名，password是连接数据库的密码，  database是你要连接你MySQL里面的哪个数据库的名字，字符集编码charset=utf8 cursor = conn.cursor()sql = cursor.execute(sql) 执行sql语句ret = cursor.fetchall()  获取查询数据 cursor.close()  关闭连接 conn.close()   关闭连接 (.center()+.center()+.center()+.center()+.center()) row ret:     (row[].center()++row[].center()++(row[]).center()++row[].center()++row[].center())这就是使用python连接MySQL的方法了 我在Windows10系统下运行正常

 一、Can't connect to MySQL server on 'localhost'(10061)?    翻译：不能连接到localhost 上的mysql?分析：这说明“localhost”计算机是存在的，但在这台机器上却没提供MySQL服务。?需要启动这台机器上的MySQL服务,如果机子负载太高没空相应请求也会产生这个错误。?解决:既然没有启动那就去启动这台机子的mysql。如果启动不成功，多数是因为你的my.ini配置的有问题。重新配置其即可。?如果觉得mysql负载异常，可以到mysql/bin 的目录下执行mysqladmin-uroot -p123 processlist来查看mysql当前的进程。二、Unknown MySQL ServerHost 'localhosadst' (11001)    翻译：未知的MySQL服务器localhosadst?分析：服务器localhosasdst 不存在。或者根本无法连接?解决：仔细检查自己论坛下面的./config.inc.php找到$dbhost重新设置为正确的mysql 服务器地址。   三、Access denied for user:'roota@localhost' (Using password: YES)    ?翻译：用户roota 访问 localhost 被拒绝（没有允许通过）?分析：造成这个错误一般数据库用户名和密码相对mysql服务器不正确?解决：仔细检查自己论坛下面的 ./config.inc.php 找到$dbuser、$dbpw核实后重新设置保存即可。    四、Access denied for user:'red@localhost' to database 'newbbs'?    翻译：用户 red在localhost 服务器上没有权限操作数据库newbbs?分析：这个提示和问题三是不同的。那个是在连接数据库的时候就被阻止了，而这个错误是在对数据库进行操作时引起的。比如在selectupdate等等。这个是因为该用户没有操作数据库相应的权力。比如select 这个操作在mysql.user.Select_priv里记录 Y 可以操作N 不可以操作。?解决：如果是自己的独立主机那么更新mysql.user 的相应用户记录，比如这里要更新的用户为red 。或者直接修改./config.inc.php 为其配置一个具有对数据库操作权限的用户?或者通过如下的命令来更新授权grantall privileges on dbname.* to 'user'@'localhost' identified by 'password'?提示：更新了mysql库中的记录一定要重启mysql服务器才能使更新生效?FLUSH PRIVILEGES;   五、No Database Selected    ?翻译：没有数据库被选择上?分析：产生的原因有两种?config.inc.php 里面$dbname设置的不对。致使数据库根本不存在，所以在$db->select_db($dbname); 时返回了false?和上面问题四是一样的，数据库用户没有select权限，同样会导致这样的错误。当你发现config.inc.php的设置没有任何问题，但还是提示这个错误，那一定就是这种情况了。?解决：对症下药?打开config.inc.php找到$dbname核实重新配置并保存?同问题四的解决方法  六、Can't open file:'xxx_forums.MYI'. (errno: 145)    翻译：不能打开xxx_forums.MYI?问题分析：?这种情况是不能打开cdb_forums.MYI 造成的，引起这种情况可能的原因有：?1、服务器非正常关机，数据库所在空间已满，或一些其它未知的原因，对数据库表造成了损坏。?2、类 unix 操作系统下直接将数据库文件拷贝移动会因为文件的属组问题而产生这个错误。?解决方法：?1、修复数据表?可以使用下面的两种方式修复数据表：（第一种方法仅适合独立主机用户）?1）使用 myisamchk ，MySQL自带了专门用户数据表检查和修复的工具 —— myisamchk 。更改当前目录到MySQL/bin 下面，一般情况下只有在这个下面才能运行 myisamchk 命令。常用的修复命令为：myisamchk-r 数据文件目录/数据表名.MYI；?2）通过 phpMyAdmin 修复，phpMyAdmin 带有修复数据表的功能，进入到某一个表中后，点击“操作”，在下方的“表维护”中点击“修复表”即可。?注意：以上两种修复方式在执行前一定要备份数据库。?2、修改文件的属组（仅适合独立主机用户）?1）复制数据库文件的过程中没有将数据库文件设置为MySQL 运行的帐号可读写（一般适用于 Linux和 FreeBSD 用户）。   七、Table'test.xxx_sessions' doesn't exist    ?翻译：xxxxx表不存在?分析：在执行sql语句时没有找到表，比如：SELECT * FROMxxx_members WHERE uid='XX' 这里如果表xxx_members不存在于$dbname库里，那么就会提示这个错误。具体可分为以下三种情况来讨论：?安装插件或者hack时修改了程序文件，而忘记了对数据库作相应的升级。?后台使用了不完全备份，导入数据时没有导入到已经安装了相应版本的论坛的数据库中。?解决： 同样对症下药，不同的原因不同的处理方法。?仔细对照插件作者提供的安装说明，把遗漏的对数据库的操作补上，如果仍然不能解决问题，那么应该怀疑该插件的可用性了。去咨询一下插件作者，或者将其卸载。?不要张冠李戴，多大的脚就穿多大的鞋。总之使得程序文件和数据库配套即可.   八、Unknown column'column_name' in 'field list'    翻译：未知的字段名column_name?分析：在执行sql语句是出现了指定表中没有的字段名称，就会出现这个错误。具体导致的原因可分为以下两种?安装插件或者hack时修改了程序文件，而忘记了对数据库作相应的升级。?程序文件和数据库不配套，比如d2.5的数据库配置给d4.1的程序来用肯定会出现这个错误。?解决： 导致的原因和问题八的1和 3是相同的，所以解决方法也一样。    九、You have an error in yourSQL syntax    翻译：有一个语法错误在你的sql中?分析：论坛标准的程序是没有sql语法错误的。所以造成这个错误的原因一般就两类?安装插件或擅自修改程序。?不同的数据库版本数据库导出导入，比如MySQL4.1的数据在导出的语句包含了MySQL4.0没有的功能，像字符集的设定，这时如果将这些sql导入到MySQL4.0的时候就会产生sql语法错误。?解决：?仔细检查看到底是哪里的错误，将其修正，实在不行就用标准程序把出错的程序替换。?在数据库备份的时候要留意，如果不打算倒入到其他版本的mysql中则不用特殊考虑，反之要特殊的设定。使用DZ4.1的后台数据备份，可以按照提示去设定想要的格式。独立主机的也可以在到处的时候将其导出为mysql4.0的格式。?mysqldump -uroot -p--default-character-set=latin1 --set-charset=gbk --skip-opt databse >test.sql   十、Duplicate entry 'xxx'for key 1    翻译：插入 xxx使索引1重复?分析：索引如果是primaryunique这两两种，那么数据表的数据对应的这个字段就必须保证其每条记录的唯一性。否则就会产生这个错误。?一般发生在对数据库写操作的时候，例如Discuz!4.1论坛程序要求所有会员的用户名username必须唯一，即username的索引是unique，这时如果强行往cdb_members表里插入一个已有的username的记录就会发上这个错误，或者将一条记录的username更新 为已有的一个username。?改变表结构的时候也有可能导致这个错误。例如 Discuz!4.0论坛的数据库中cdb_members.username的索引类型是index这个时候是允许有相同username的记录存在的，在升级到4.1的时候，因为要将username的索引由原来的index变 为unique。如果这时cdb_members里存在有相同的username的记录，那么就会引发这个错误。?导出数据据时有时会因为一些原因（作者目前还不清楚）导致同一条记录被重复导出，那么这个备份数据在导入的时候出现这个错误是在所难免的了。?修改了auto_increment的值，致使“下一个Autoindex”为一条已经存在的记录?解决： 两种思路，一是破坏掉唯一性的索引。二是把重复的数据记录干掉，只保留一条。很显然第一种思路是不可取的。那么按照二的思路我们得出以下几种解决方法，对应上面的i iiiii?略?按照错误提示里的信息到数据库中将重复的记录删除，仅保留一条即可。之后继续执行升级操作。?这种情况发生的概率很小，可以用文本编辑器打开备份文档，查找重复的信息。将其多余的拿掉，仅保留一条即可。?查询出表中auto_increment最大的一条记录，设置auto_incerment比其大一即可。?PS：repaire table "表名“，可以暂时解决问题。

下面两个函数的使用和FIND_IN_SET一样,使用时只需要把FIND_IN_SET换成FIND_PART_IN_SET或FIND_ALL_PART_IN_SET例如某字段里是为1,2,3,4,5 使用方法:第一种,传入1,3,6  可以查出来select * from XXX where FIND_PART_IN_SET('1,3,6','1,2,3,4,5')第二种,传入1,3,6  查不出来select * from XXX where FIND_ALL_PART_IN_SET('1,3,6','1,2,3,4,5')函数:第一种:只要包含其中一个就可以被查出来CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text) RETURNS textBEGIN #传入两个逗号分割的字符串，判断第二个字符串是否包含第一个字符串split之后的单个DECLARE CURRENTINDEX INT;#当前下标DECLARE CURRENTSTR text;DECLARE result int;set result = 0;set CURRENTINDEX = 0;set CURRENTSTR = '';IF str1 IS NOT NULL AND str1 != '' THEN SET CURRENTINDEX = LOCATE(',',str1); WHILE CURRENTINDEX > 0 DO SET CURRENTSTR = substring(str1,1,CURRENTINDEX-1); if FIND_IN_SET(CURRENTSTR,str2) THEN  set result = 1; end if; SET str1 = substring(str1,CURRENTINDEX+1); SET CURRENTINDEX = LOCATE(',',str1); END WHILE; #只传一个 和 最后无逗号的情况 IF LENGTH(str1) > 0 THEN if FIND_IN_SET(str1,str2) THEN  set result = 1; end if; END IF;END IF;RETURN result;END;第二种:必须全部包含才可以被查出来CREATE DEFINER = `root`@`%` FUNCTION `NewProc`(str1 text,str2 text) RETURNS textBEGIN #传入两个逗号分割的字符串，判断第二个字符串是否全部包含第一个字符串split之后的单个DECLARE CURRENTINDEX INT;#当前下标DECLARE CURRENTSTR text;DECLARE RESULT int;DECLARE TOTALCOUNT int;DECLARE TRUECOUNT int;set RESULT = 0;set CURRENTINDEX = 0;set CURRENTSTR = '';set TOTALCOUNT = 0;set TRUECOUNT = 0;IF str1 IS NOT NULL AND str1 != '' THEN SET CURRENTINDEX = LOCATE(',',str1); WHILE CURRENTINDEX > 0 DO SET TOTALCOUNT = TOTALCOUNT + 1; SET CURRENTSTR = substring(str1,1,CURRENTINDEX-1); if FIND_IN_SET(CURRENTSTR,str2) THEN  SET TRUECOUNT = TRUECOUNT + 1; end if; SET str1 = substring(str1,CURRENTINDEX+1); SET CURRENTINDEX = LOCATE(',',str1); END WHILE; #只传一个 和 最后无逗号的情况 IF LENGTH(str1) > 0 THEN SET TOTALCOUNT = TOTALCOUNT + 1; if FIND_IN_SET(str1,str2) THEN  SET TRUECOUNT = TRUECOUNT + 1; end if; END IF;END IF;IF TOTALCOUNT > 0 AND TRUECOUNT = TOTALCOUNT THEN SET RESULT = 1;END IF;RETURN result;END;

使用前将工具包拖到/opt目录下即可写一个脚本文件，将以下内容复制进去，使用source或者. 执行脚本#!/bin/bashmount /dev/cdrom /mnt &> /dev/nullecho "---正在安装环境依赖包---"yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmakeecho "---正在配置软件模块---"cd /opttar zxvf mysql-5.7.17.tar.gztar zxvf boost_1_59_0.tar.gzcd /optmv boost_1_59_0 /usr/local/boost cd /opt/mysql-5.7.17/cmake \-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \-DSYSCONFDIR=/etc \-DSYSTEMD_PID_DIR=/usr/local/mysql \-DDEFAULT_CHARSET=utf8  \-DDEFAULT_COLLATION=utf8_general_ci \-DWITH_EXTRA_CHARSETS=all \-DWITH_INNOBASE_STORAGE_ENGINE=1 \-DWITH_ARCHIVE_STORAGE_ENGINE=1 \-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \-DMYSQL_DATADIR=/usr/local/mysql/data \-DWITH_BOOST=/usr/local/boost \-DWITH_SYSTEMD=1echo "---编译安装中---"make && make installecho "---创建mysql用户---"useradd -M -s /sbin/nologin  mysqlecho "---修改mysql 配置文件---"echo '[client]port = 3306socket=/usr/local/mysql/mysql.sock[mysqld]user = mysqlbasedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport = 3306character-set-server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket=/usr/local/mysql/mysql.sockbind-address = 0.0.0.0skip-name-resolvemax_connections=2048default-storage-engine=INNODBmax_allowed_packet=16Mserver-id = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES' > /etc/my.cnfecho "---更改mysql安装目录和配置文件的属主属组---"chown -R mysql:mysql /usr/local/mysql/chown mysql:mysql /etc/my.cnfecho "---设置路径环境变量---"echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile source /etc/profileecho "---正在初始化数据库---"cd /usr/local/mysql/bin/./mysqld \--initialize-insecure \--user=mysql \--basedir=/usr/local/mysql \--datadir=/usr/local/mysql/dataecho "---添加mysqld系统服务---"cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ systemctl daemon-reload              systemctl start mysqld.service  systemctl enable mysqld         netstat -anpt | grep 3306       echo "---修改mysql的登录密码---"mysqladmin -u root -p password "123456"echo "初始密码已设为：123456"echo "---正在登录---"mysql -uroot -p123456

MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。下表中列出了 MySQL 中的日期与时间类型。类型名称日期格式日期范围存储需求YEARYYYY1901 ~ 21551 个字节TIMEHH:MM:SS-838:59:59 ~ 838:59:593 个字节DATEYYYY-MM-DD1000-01-01 ~ 9999-12-33 个字节DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:598 个字节TIMESTAMPYYYY-MM-DD HH:MM:SS1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC4 个字节YEAR 类型YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 '1901'～'2155'。输入格式为 'YYYY' 或者 YYYY，例如，输入 '2010' 或 2010，插入数据库的值均为 2010。以 2 位字符串格式表示的 YEAR，范围为 '00' 到 '99'。'00'～'69' 和 '70'～'99' 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。'0' 与 '00' 的作用相同。插入超过取值范围的值将被转换为 2000。以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，大家可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 '0' 或 '00'，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。TIME 类型TIME 类型用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。TIME 类型的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。可以使用各种格式指定 TIME 值，如下所示。'D HH：MM：SS' 格式的字符串。还可以使用这些“非严格”的语法：'HH：MM：SS'、'HH：MM'、'D HH' 或 'SS'。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。'HHMMSS' 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，'101112' 被理解为'10：11：12'，但是 '106112' 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 '1112' 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 '12' 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，'11：12' 表示 11：12：00，而不是 00：11：12。DATE 类型DATE 类型用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：以 'YYYY-MM-DD' 或者 'YYYYMMDD' 字符中格式表示的日期，取值范围为 '1000-01-01'～'9999-12-3'。例如，输入 '2015-12-31' 或者 '20151231'，插入数据库的日期为2015-12-31。以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，'98-11-31'、'98.11.31'、'98/11/31'和'98@11@31' 是等价的，这些值也可以正确地插入数据库。DATETIME 类型DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH：MM：SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，'98-12-31 11：30：45'、'98.12.31 11+30+35'、'98/12/31 11*30*45' 和 '98@12@31 11^30^45' 是等价的，这些值都可以正确地插入数据库。TIMESTAMP 类型TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

遇到一个需求，需要在MySql的字段中截取一段字符串中的特定字符，类似于正则表达式的截取，苦于没有合适的方法，百度之后终于找到一个合适的方法：substring_index('www.sqlstudy.com.cn', '.', -2)强烈推荐该方法获取含有特定字符的数据。　　substring_index(input,split,index)：input为要截取的字符，split为分隔符，Index为要截取第index个分隔符左（index为正）或右(index为负)的字符串。　　拿个人的一个字符串举例：'Provider="RiskManagement" finalScore="65" RGID="100397278"'      我要获取finalScore的值：-- 1-获取finalScore右边的字符select substring_index('Provider="RiskManagement" finalScore="65" RGID="100397278"','finalScore="',-1);-- 2-再获取" RGID="左边的字符select substring_index(substring_index('Provider="RiskManagement" finalScore="65" RGID="100397278"','finalScore="',-1),'" RGID="',1);结果1：65" RGID="100397278"　　结果2：65MySQL 字符串截取函数：left(), right(), substring(), substring_index()。还有 mid(), substr()。其中，mid(), substr() 等价于 substring() 函数，substring() 的功能非常强大和灵活。1. 字符串截取：left(str, length)mysql> select left('sqlstudy.com', 3); +-------------------------+ | left('sqlstudy.com', 3) | +-------------------------+ | sql | +-------------------------+2. 字符串截取：right(str, length)mysql> select right('sqlstudy.com', 3); +--------------------------+ | right('sqlstudy.com', 3) | +--------------------------+ | com | +--------------------------+3. 字符串截取：substring(str, pos); substring(str, pos, len)　　3.1 从字符串的第 4 个字符位置开始取，直到结束。mysql> select substring('sqlstudy.com', 4); +------------------------------+ | substring('sqlstudy.com', 4) | +------------------------------+ | study.com | +------------------------------+3.2 从字符串的第 4 个字符位置开始取，只取 2 个字符。mysql> select substring('sqlstudy.com', 4, 2); +---------------------------------+ | substring('sqlstudy.com', 4, 2) | +---------------------------------+ | st | +---------------------------------+ 3.3 从字符串的第 4 个字符位置（倒数）开始取，直到结束。\mysql> select substring('sqlstudy.com', -4); +-------------------------------+ | substring('sqlstudy.com', -4) | +-------------------------------+ | .com | +-------------------------------+3.4 从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。mysql> select substring('sqlstudy.com', -4, 2); +----------------------------------+ | substring('sqlstudy.com', -4, 2) | +----------------------------------+ | .c | +----------------------------------+ 我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。4. 字符串截取：substring_index(str,delim,count)　　4.1 截取第二个 '.' 之前的所有字符。mysql> select substring_index('www.sqlstudy.com.cn', '.', 2); +------------------------------------------------+ | substring_index('www.sqlstudy.com.cn', '.', 2) | +------------------------------------------------+ | www.sqlstudy | +------------------------------------------------+ 4.2 截取第二个 '.' （倒数）之后的所有字符。mysql> select substring_index('www.sqlstudy.com.cn', '.', -2); +-------------------------------------------------+ | substring_index('www.sqlstudy.com.cn', '.', -2) | +-------------------------------------------------+ | com.cn | +-------------------------------------------------+ 　4.3 如果在字符串中找不到 delim 参数指定的值，就返回整个字符串mysql> select substring_index('www.sqlstudy.com.cn', '.coc', 1); +---------------------------------------------------+ | substring_index('www.sqlstudy.com.cn', '.coc', 1) | +---------------------------------------------------+ | www.sqlstudy.com.cn | +---------------------------------------------------+4.4 截取一个表某个字段数据的中间值 如该字段数据为 1,2,3mysql> select substring_index(substring_index(该字段, ',', 2) , ',', -1) from 表名; +--------------------------------------------------------------+ | substring_index(substring_index(该字段, ',', 2); , ',', -1)| +--------------------------------------------------------------+ | 2 | +--------------------------------------------------------------+

MySQL 中使用浮点数和定点数来表示小数。浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。下表中列出了 MySQL 中的小数类型和存储需求。类型名称说明存储需求FLOAT单精度浮点数4 个字节DOUBLE双精度浮点数8 个字节DECIMAL (M, D)，DEC压缩的“严格”定点数M+2 个字节DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。FLOAT 类型的取值范围如下：有符号的取值范围：-3.402823466E+38～-1.175494351E-38。无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。DOUBLE 类型的取值范围如下：有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。提示：不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。最后再强调一下：在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好，另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

### 引言 熟悉MySQL的朋友应该都知道，MySQL集群主从间数据同步机制十分完善。令人惊喜的是，ClickHouse作为近年来炙手可热的大数据分析引擎也可以挂载为MySQL的从库，作为MySQL的 "协处理器" 面向OLAP场景提供高效数据分析能力。早先的方案比较直截了当，通过第三方插件将所有MySQL上执行的操作进行转化，然后在ClickHouse端逐一回放达到数据同步。终于在2020年下半年，Yandex 公司在 ClickHouse 社区发布了MaterializeMySQL引擎，支持从MySQL全量及增量实时数据同步。MaterializeMySQL引擎目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。 ### 基础概念 - **MySQL & ClickHouse** MySQL一般特指完整的MySQL RDBMS，是开源的关系型数据库管理系统，目前属于Oracle公司。MySQL凭借不断完善的功能以及活跃的开源社区，吸引了越来越多的企业和个人用户。 ClickHouse是由Yandex公司开源的面向OLAP场景的分布式列式数据库。ClickHouse具有实时查询，完整的DBMS及高效数据压缩，支持批量更新及高可用。此外，ClickHouse还较好地兼容SQL语法并拥有开箱即用等诸多优点。 - **Row Store & Column Store** MySQL存储采用的是Row Store，表中数据按照 Row 为逻辑存储单元在存储介质中连续存储。这种存储方式适合随机的增删改查操作，对于按行查询较为友好。但如果选择查询的目标只涉及一行中少数几个属性，Row 存储方式也不得不将所有行全部遍历再筛选出目标属性，当表属性较多时查询效率通常较低。尽管索引以及缓存等优化方案在 OLTP 场景中能够提升一定的效率，但在面对海量数据背景的 OLAP 场景就显得有些力不从心了。 ClickHouse 则采用的是 Column Store，表中数据按照Column为逻辑存储单元在存储介质中连续存储。这种存储方式适合采用 SIMD (Single Instruction Multiple Data) 并发处理数据，尤其在表属性较多时查询效率明显提升。由于列存方式中物理相邻的数据类型通常相同，因此天然适合数据压缩，从而达到极致的数据压缩比。  ### 使用方法 - 部署Master-MySQL 开启BinLog功能：ROW模式 开启GTID模式：解决位点同步时MySQL主从切换问题（BinLog reset导致位点失效） ```shell # my.cnf关键配置 gtid_mode=ON enforce_gtid_consistency=1 binlog_format=ROW ``` - 部署Slave-ClickHouse 获取 [ClickHouse/Master](https://github.com/ClickHouse/ClickHouse) 代码编译安装 推荐使用`GCC-10.2.0`，`CMake 3.15`，`ninja1.9.0`及以上 - 创建Master-MySQL中database及table ```mysql creat databases master_db; use master_db; CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8; # 插入几条数据 INSERT INTO runoob_tbl (runoob_, runoob_author, submission_date) VALUES ("MySQL-learning", "Bob", NOW()); INSERT INTO runoob_tbl (runoob_, runoob_author, submission_date) VALUES ("MySQL-learning", "Tim", NOW()); ``` - 创建 Slave-ClickHouse 中 MaterializeMySQL database ```sql # 开启materialize同步功能 SET allow_experimental_database_materialize_mysql=1; # 创建slave库，参数分别是("mysqld服务地址", "待同步库名", "授权账户", "密码") CREATE DATABASE slave_db ENGINE = MaterializeMySQL('192.168.6.39:3306', 'master_db', 'root', '3306123456'); ``` 此时可以看到ClickHouse中已经有从MySQL中同步的数据了： ```mysql DESKTOP:) select * from runoob_tbl; SELECT * FROM runoob_tbl Query id: 6e2b5f3b-0910-4d29-9192-1b985484d7e3 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┐ │ 1 │ MySQL-learning │ Bob │ 2021-01-06 │ └───────────┴────────────────┴───────────────┴─────────────────┘ ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┐ │ 2 │ MySQL-learning │ Tim │ 2021-01-06 │ └───────────┴────────────────┴───────────────┴─────────────────┘ 2 rows in set. Elapsed: 0.056 sec. ``` ### 工作原理 - BinLog Event MySQL中BinLog Event主要包含以下几类： ```mysql 1. MYSQL_QUERY_EVENT　　　　-- DDL 2. MYSQL_WRITE_ROWS_EVENT　-- insert 3. MYSQL_UPDATE_ROWS_EVENT -- update 4. MYSQL_DELETE_ROWS_EVENT -- delete ``` 事务提交后，MySQL 将执行过的 SQL 处理 BinLog Event，并持久化到 BinLog 文件 ClickHouse通过消费BinLog达到数据同步，过程中主要考虑３个方面问题： 1、DDL兼容：由于ClickHouse和MySQL的数据类型定义有区别，DDL语句需要做相应转换 2、Delete/Update 支持：引入`_version`字段，控制版本信息 3、Query 过滤：引入`_sign`字段，标记数据有效性 - DDL操作 对比一下MySQL的DDL语句以及在ClickHouse端执行的DDL语句： ```mysql mysql> show create table runoob_tbl\G; *************************** 1. row *************************** Table: runoob_tbl Create Table: CREATE TABLE `runoob_tbl` ( `runoob_id` int unsigned NOT NULL AUTO_INCREMENT, `runoob_` varchar(100) NOT NULL, `runoob_author` varchar(40) NOT NULL, `submission_date` date DEFAULT NULL, PRIMARY KEY (`runoob_id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) --------------------------------------------------------------- cat /metadata/slave_db/runoob_tbl.sql ATTACH TABLE _ UUID '14dbff59-930e-4aa8-9f20-ccfddaf78077' ( `runoob_id` UInt32, `runoob_` String, `runoob_author` String, `submission_date` Nullable(Date), `_sign` Int8 MATERIALIZED 1, `_version` UInt64 MATERIALIZED 1 ) ENGINE = ReplacingMergeTree(_version) PARTITION BY intDiv(runoob_id, 4294967) ORDER BY tuple(runoob_id) SETTINGS index_granularity = 8192 ``` 可以看到： 1、在DDL转化时默认增加了2个隐藏字段：_sign(-1删除, 1写入) 和 _version(数据版本) 2、默认将表引擎设置为 ReplacingMergeTree，以 _version 作为 column version 3、原DDL主键字段 runoob_id 作为ClickHouse排序键和分区键 此外还有许多DDL处理，比如增加列、索引等，相应代码在`Parsers/MySQL` 目录下。 - Delete/Update操作 Update： ```mysql # Mysql端： UPDATE runoob_tbl set runoob_author='Mike' where runoob_id=2; mysql> select * from runoob_tbl; +-----------+----------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+----------------+---------------+-----------------+ | 1 | MySQL-learning | Bob | 2021-01-06 | | 2 | MySQL-learning | Mike | 2021-01-06 | +-----------+----------------+---------------+-----------------+ 2 rows in set (0.00 sec) ---------------------------------------------------------------- # ClickHouse端： DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id; SELECT *, _sign, _version FROM runoob_tbl ORDER BY runoob_id ASC Query id: c5f4db0a-eff6-4b49-a429-b55230c26301 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐ │ 1 │ MySQL-learning │ Bob │ 2021-01-06 │ 1 │ 2 │ │ 2 │ MySQL-learning │ Mike │ 2021-01-06 │ 1 │ 4 │ │ 2 │ MySQL-learning │ Tim │ 2021-01-06 │ 1 │ 3 │ └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘ 3 rows in set. Elapsed: 0.003 sec. ``` 可以看到，ClickHouse数据也实时同步了更新操作。 Delete： ```mysql # Mysql端 mysql> DELETE from runoob_tbl where runoob_id=2; mysql> select * from runoob_tbl; +-----------+----------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+----------------+---------------+-----------------+ | 1 | MySQL-learning | Bob | 2021-01-06 | +-----------+----------------+---------------+-----------------+ 1 row in set (0.00 sec) ---------------------------------------------------------------- # ClickHouse端 DESKTOP:) select *, _sign, _version from runoob_tbl order by runoob_id; SELECT *, _sign, _version FROM runoob_tbl ORDER BY runoob_id ASC Query id: e9cb0574-fcd5-4336-afa3-05f0eb035d97 ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐ │ 1 │ MySQL-learning │ Bob │ 2021-01-06 │ 1 │ 2 │ └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘ ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐ │ 2 │ MySQL-learning │ Mike │ 2021-01-06 │ -1 │ 5 │ └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘ ┌─runoob_id─┬─runoob_title───┬─runoob_author─┬─submission_date─┬─_sign─┬─_version─┐ │ 2 │ MySQL-learning │ Mike │ 2021-01-06 │ 1 │ 4 │ │ 2 │ MySQL-learning │ Tim │ 2021-01-06 │ 1 │ 3 │ └───────────┴────────────────┴───────────────┴─────────────────┴───────┴──────────┘ 4 rows in set. Elapsed: 0.002 sec. ``` 可以看到，删除id为2的行只是额外插入了`_sign == -1`的一行记录，并没有真正删掉。 - 日志回放 MySQL 主从间数据同步时Slave节点将 BinLog Event 转换成相应的SQL语句，Slave 模拟 Master 写入。类似地，传统第三方插件沿用了MySQL主从模式的BinLog消费方案，即将 Event 解析后转换成 ClickHouse 兼容的 SQL 语句，然后在 ClickHouse 上执行（回放），但整个执行链路较长，通常性能损耗较大。不同的是，MaterializeMySQL 引擎提供的内部数据解析以及回写方案隐去了三方插件的复杂链路。回放时将 BinLog Event 转换成底层 Block 结构，然后直接写入底层存储引擎，接近于物理复制。此方案可以类比于将 BinLog Event 直接回放到 InnoDB 的 Page 中。 ### **同步策略** - 位点同步 v20.9.1版本前是基于位点同步的，ClickHouse每消费完一批 BinLog Event，就会记录 Event 的位点信息到 `.metadata` 文件: ```mysql [FavonianKong@Wsl[20:42:37]slave_db] $ cat ./.metadata Version: 2 Binlog File: mysql-bin.000003 Binlog Position:355005999 Data Version: 5 ``` 这样当 ClickHouse 再次启动时，它会把 {‘mysql-bin.000003’, 355005999} 二元组通过协议告知 MySQL Server，MySQL 从这个位点开始发送数据： ``` s1> ClickHouse 发送 {‘mysql-bin.000003’, 355005999} 位点信息给 MySQL s2> MySQL 找到本地 mysql-bin.000003 文件并定位到 355005999 偏移位置，读取下一个 Event 发送给 ClickHouse s3> ClickHouse 接收 binlog event 并完成同步操作 s4> ClickHouse 更新 .metadata位点 ``` **存在问题：** 如果MySQL Server是一个集群，通过VIP对外服务，MaterializeMySQL创建 database 时 host 指向的是VIP，当集群主从发生切换后，`{Binlog File, Binlog Position}` 二元组不一定是准确的，因为BinLog可以做reset操作。 ``` s1> ClickHouse 发送 {'mysql-bin.000003’, 355005999} 给集群新主 MySQL s2> 新主 MySQL 发现本地没有 mysql-bin.000003 文件，因为它做过 reset master 操作，binlog 文件是 mysql-bin.000001 s3> 产生错误复制 ``` 为了解决这个问题，v20.9.1版本后上线了 GTID 同步模式，废弃了不安全的位点同步模式。 - GTID同步 GTID模式为每个 event 分配一个全局唯一ID和序号，直接告知 MySQL 这个 GTID 即可，于是`.metadata`变为: ```mysql [FavonianKong@Wsl[21:30:19]slave_db] Version: 2 Binlog File: mysql-bin.000003 Executed GTID: 0857c24e-4755-11eb-888c-00155dfbdec7:1-783 Binlog Position:355005999 Data Version: 5 ``` 其中 `0857c24e-4755-11eb-888c-00155dfbdec7` 是生成 Event的主机`UUID`，`1-783`是已经同步的event区间 于是流程变为: ``` s1> ClickHouse 发送 GTID:0857c24e-4755-11eb-888c-00155dfbdec7:1-783 给 MySQL s2> MySQL 根据 GTID 找到本地位点，读取下一个 Event 发送给 ClickHouse s3> ClickHouse 接收 BinLog Event 并完成同步操作 s4> ClickHouse 更新 .metadata GTID信息 ``` ### 源码分析 - 概述 在最新源码 (v20.13.1.1) 中，ClickHouse 官方对 DatabaseMaterializeMySQL 引擎的相关源码进行了重构，并适配了 GTID 同步模式。ClickHouse 整个项目的入口 `main` 函数在 `/ClickHouse/programs/main.cpp` 文件中，主程序会根据接收指令将任务分发到 `ClickHouse/programs` 目录下的子程序中处理。本次分析主要关注 Server 端 `MaterializeMySQL` 引擎的工作流程。 - 源码目录 与 MaterializeMySQL 相关的主要源码路径： ```c++ ClickHouse/src/databases/MySQL //MaterializeMySQL存储引擎实现 ClickHouse/src/Storages/ //表引擎实现 ClickHouse/src/core/MySQL* //复制相关代码 ClickHouse/src/Interpreters/ //Interpreters实现，SQL的rewrite也在这里处理 ClickHouse/src/Parsers/MySQL //解析部分实现,DDL解析等相关处理在这里 ``` - 服务端主要流程 ClickHouse 使用 POCO 网络库处理网络请求，Client连接的处理逻辑在 ClickHouse/src/Server/*Handler.cpp 的 hander方法里。以TCP为例，除去握手，初始化上下文以及异常处理等相关代码，主要逻辑可以抽象成: ```c++ // ClickHouse/src/Server/TCPHandler.cpp TCPHandler.runImpl() { ... while(true) { ... if (!receivePacket()) //line 184 continue /// Processing Query //line 260 state.io = executeQuery(state.query, *query_context, ...); ... } ``` - 数据同步预处理 Client发送的SQL在executeQuery函数处理，主要逻辑简化如下： ```c++ // ClickHouse/src/Interpreters/executeQuery.cpp static std::tuple executeQueryImpl(...) { ... // line 354，解析器可配置 ast = parseQuery(...); ... // line 503, 根据语法树生成interpreter auto interpreter = InterpreterFactory::get(ast, context, ...); ... // line 525, 执行器interpreter执行后返回结果 res = interpreter->execute(); ... } ``` 主要有三点： 1、解析SQL语句并生成语法树 AST 2、InterpreterFactory 工厂类根据 AST 生成执行器 3、interpreter->execute() 跟进第三点，看看 InterpreterCreateQuery 的 excute() 做了什么： ```c++ // ClickHouse/src/Interpreters/InterpreterCreateQuery.cpp BlockIO InterpreterCreateQuery::execute() { ... // CREATE | ATTACH DATABASE if (!create.database.empty() && create.table.empty()) // line 1133, 当使用MaterializeMySQL时，会走到这里建库 return createDatabase(create); } ``` 这里注释很明显，主要执行 CREATE 或 ATTACH DATABASE，继续跟进 createDatabase() 函数： ```c++ // ClickHouse/src/Interpreters/InterpreterCreateQuery.cpp BlockIO InterpreterCreateQuery::createDatabase(ASTCreateQuery & create) { ... // line 208, 这里会根据 ASTCreateQuery 参数，从 DatabaseFactory 工厂获取数据库对象 // 具体可以参考 DatabasePtr DatabaseFactory::getImpl() 函数 DatabasePtr database = DatabaseFactory::get(create, metadata_path, ...); ... // line 253, 多态调用，在使用MaterializeMySQL时 // 上方get函数返回的是 DatabaseMaterializeMySQL database->loadStoredObjects(context, ...); } ``` 到这里，相当于将任务分发给DatabaseMaterializeMySQL处理，接着跟踪 loadStoredObjects 函数： ```c++ //ClickHouse/src/Databases/MySQL/DatabaseMaterializeMySQL.cpp template void DatabaseMaterializeMySQL::loadStoredObjects(Context & context, ...) { Base::loadStoredObjects(context, has_force_restore_data_flag, force_attach); try { // line87, 这里启动了materialize的同步线程 materialize_thread.startSynchronization(); started_up = true; } catch (...) ... } ``` 跟进startSynchronization() 绑定的执行函数： ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp void MaterializeMySQLSyncThread::synchronization() { ... // 全量同步在 repareSynchronized() 进行 if (std::optional metadata = prepareSynchronized()) { while (!isCancelled()) { UInt64 max_flush_time = settings->max_flush_data_time; BinlogEventPtr binlog_event = client.readOneBinlogEvent(...); { //增量同步侦听binlog_envent if (binlog_event) onEvent(buffers, binlog_event, *metadata); } } } ... } ``` - 全量同步 MaterializeMySQLSyncThread::prepareSynchronized 负责DDL和全量同步，主要流程简化如下： ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp std::optional MaterializeMySQLSyncThread::prepareSynchronized() { while (!isCancelled()) { ... try { //构造函数内会获取MySQL的状态、MySQL端的建表语句， MaterializeMetadata metadata(connection, ...); // line345, DDL相关转换 metadata.transaction(position, [&]() { cleanOutdatedTables(database_name, global_context); dumpDataForTables(connection, metadata, global_context, ...); }); return metadata; } ... } } ``` ClickHouse作为MySQL从节点，在MaterializeMetadata构造函数中对MySQL端进行了一系列预处理： 1、将打开的表关闭，同时对表加上读锁并启动事务 2、TablesCreateQuery通过SHOW CREATE TABLE 语句获取MySQL端的建表语句 3、获取到建表语句后释放表锁 继续往下走，执行到 metadata.transaction() 函数，该调用传入了匿名函数作为参数，一直跟进该函数会发现最终会执行匿名函数，也就是cleanOutdatedTables以及dumpDataForTables函数，主要看一下 dumpDataForTables 函数： ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp static inline void dumpDataForTables(...) { ... //line293, 这里执行建表语句 tryToExecuteQuery(..., query_context, database_name, comment); } ``` 继续跟踪 tryToExecuteQuery 函数，会调用到 executeQueryImpl() 函数，上文提到过这个函数，但这次我们的上下文信息变了，生成的执行器发生变化，此时会进行 DDL 转化以及 dump table 等操作： ```c++ // ClickHouse/src/Interpreters/executeQuery.cpp static std::tuple executeQueryImpl(...) { ... // line 354，解析器可配置 ast = parseQuery(...); ... // line 503，这里跟之前上下文信息不同，生成interpreter也不同 auto interpreter = InterpreterFactory::get(ast,context, ...); ... // line 525, 执行器interpreter执行后返回结果 res = interpreter->execute(); ... } ``` 此时 InterpreterFactory 返回 InterpreterExternalDDLQuery，跟进去看 execute 函数做了什么： ```c++ // ClickHouse/src/Interpreters/InterpreterExternalDDLQuery.cpp BlockIO InterpreterExternalDDLQuery::execute() { ... if (external_ddl_query.from->name == "MySQL") { #ifdef USE_MYSQL ... // line61, 当全量复制执行DDL时，会执行到这里 else if (...->as()) return MySQLInterpreter::InterpreterMySQLCreateQuery( external_ddl_query.external_ddl, cogetIdentifierName(arguments[0]), getIdentifierName(arguments[1])).execute(); #endif } ... return BlockIO(); } ``` 继续跟进去发现 getIdentifierName(arguments[1])).execute() 会rewrite DDL： ```c++ // ClickHouse/src/Interpreters/MySQL/InterpretersMySQLDDLQuery.cpp ASTs InterpreterCreateImpl::getRewrittenQueries(...) { ... // 检查是否存在primary_key, 没有直接报错 if (primary_keys.empty()) throw Exception("cannot be materialized, no primary keys.", ...); ... // 添加 _sign 和 _version 列. auto sign_column_name = getUniqueColumnName(columns_name_and_type, "_sign"); auto version_column_name = getUniqueColumnName(columns_name_and_type, "_version"); // 这里悄悄把建表引擎修改成了ReplacingMergeTree storage->set(storage->engine, makeASTFunction("ReplacingMergeTree", ...)); ... return ASTs{rewritten_query}; } ``` 完成DDL转换之后就会去执行新的DDL语句，完成建表操作，再回到 dumpDataForTables： ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp static inline void dumpDataForTables(...) { ... //line293, 这里执行建表语句 tryToExecuteQuery(..., query_context, database_name, comment); ... // line29, 这里开始 dump 数据并存放到MySQLBlockInputStream MySQLBlockInputStream input(connection, ...) } ``` - 增量同步 还记得startSynchronization() 绑定的执行函数吗？全量同步分析都是在 prepareSynchronized()进行的，那增量更新呢？ ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp void MaterializeMySQLSyncThread::synchronization() { ... // 全量同步在 repareSynchronized() 进行 if (std::optional metadata = prepareSynchronized()) { while (!isCancelled()) { UInt64 max_flush_time = settings->max_flush_data_time; BinlogEventPtr binlog_event = client.readOneBinlogEvent(...); { //增量同步侦听binlog_envent if (binlog_event) onEvent(buffers, binlog_event, *metadata); } } } ... } ``` 可以看到，while 语句里有一个 binlog_event 的侦听函数，用来侦听 MySQL 端 BinLog 日志变化，一旦 MySQL 端执行相关操作，其 BinLog 日志会更新并触发 binlog_event，增量更新主要在这里进行。 ```c++ // ClickHouse/src/Databases/MySQL/MaterializeMySQLSyncThread.cpp void MaterializeMySQLSyncThread::onEvent(Buffers & buffers, const BinlogEventPtr & receive_event, MaterializeMetadata & metadata) { // 增量同步通过监听binlog event实现，目前支持四种event：MYSQL_WRITE_ROWS_EVENT、 // MYSQL_UPDATE_ROWS_EVENT、MYSQL_DELETE_ROWS_EVENT 和 MYSQL_QUERY_EVENT // 具体的流程可以查找对应的 onHandle 函数, 不在此详细分析 if (receive_event->type() == MYSQL_WRITE_ROWS_EVENT){...} else if (receive_event->type() == MYSQL_UPDATE_ROWS_EVENT){...} else if (receive_event->type() == MYSQL_DELETE_ROWS_EVENT){...} else if (receive_event->type() == MYSQL_QUERY_EVENT){...} else {/* MYSQL_UNHANDLED_EVENT*/} } ``` ### 小结 MaterializeMySQL 引擎是 ClickHouse 官方2020年主推的特性，由于该特性在生产环境中属于刚需且目前刚上线不久，整个模块处于高速迭代的状态，因此有许多待完善的功能。例如复制过程状态查看以及数据的一致性校验等。感兴趣的话可参考Github上的2021-Roadmap，里面会更新一些社区最近得计划。以上内容如有理解错误还请指正。 ### 引用 - ClickHouse社区文档 - ClickHouse社区源码 - MySQL实时复制与实现 - MaterializeMySQL引擎分析

新得一年，小云妹我又带着数据库的新干货来了，高峰期读请求太多怎么办？不要慌，云数据库 RDS for MySQL支持一键开通读写分离，轻松应对业务高峰期详细操作，云图说为您详解~云数据库 RDS for MySQL 介绍页入口：https://www.huaweicloud.com/product/mysql.html云数据库 RDS for MySQL成长地图入口：https://support.huaweicloud.com/rds/index.html【往期回顾】●【第一期】初始华为云关系型数据库RDS●【第二期】万万没想到，云数据库MySQL原来是这样的！●【第三期】这些购买云数据库MySQL的技能实用到哭，你确定不来看看？●【第四期】云数据库MySQL一键实现弹性扩展是种什么样的体验？●【第五期】太给力了！认识云数据库PostgreSQL只需要这三步●【第六期】这才是云数据库SQL Server正确的打开方式●【第七期】数据库的私人医生——云DBA●【第八期】小云妹带你快速玩转关系型数据库实例操作（一）●【第九期】小云妹带你快速玩转关系型数据库实例操作（二）●【第十期】 云数据库 RDS for MySQL 内核小版本升级攻略

2020-12-29：mysql中，innodb表里，某一条数据删除了之后，这条数据会被真实的擦掉吗，还是删除了关系？#福大大架构师每日一题#

前言本文主要给大家介绍了MySQL中在将浮点型转字符型的时候遇到的一个问题，分享出来供大家参考学习，一 问题描述今天遇到一个刷数据的需求，是修改产品的重量（字段类型为float），修改了产品的重量之后，是需要记录到日志表中的(字段类型为varchar)，表结构如下:临时刷数据表：CREATE TABLE `temp_170830` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `goods_sn` varchar(255) NOT NULL DEFAULT '' COMMENT '产品编码',  `goods_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '产品重量',  `actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '实际重量',  `new_actual_weight` float(9,4) NOT NULL DEFAULT '0.0000' COMMENT '新的实际重量',  `create_user` varchar(30) NOT NULL DEFAULT '' COMMENT '创建人',  PRIMARY KEY (`id`),  KEY `idx_goods_sn` (`goods_sn`) ) ENGINE=InnoDB AUTO_INCREMENT=8192 DEFAULT CHARSET=utf8 COMMENT='临时刷重量表';日志表：CREATE TABLE `log_weight` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',  `goods_sn` varchar(50) NOT NULL DEFAULT '' COMMENT '产品编码',  `which_col` varchar(100) NOT NULL DEFAULT '' COMMENT '修改字段',  `old_value` varchar(50) NOT NULL DEFAULT '0.00' COMMENT '更新前值',  `new_value` varchar(50) NOT NULL DEFAULT '0.00' COMMENT '更新后值',  `update_user` varchar(100) NOT NULL DEFAULT '' COMMENT '创建人',  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `wh_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录修改时间',  PRIMARY KEY (`id`),  KEY `idx_goods_sn` (`goods_sn`),  KEY `idx_update_user` (`update_user`),  KEY `wh_update_time` (`wh_update_time`) ) ENGINE=InnoDB AUTO_INCREMENT=14601620 DEFAULT CHARSET=utf8 COMMENT='重量修改日志';如上面建的表所示，我需要将temp_170830表的actual_weight和new_actual_weight字段分别刷入log_weight表的old_value和new_value字段，SQL语句如下：INSERT INTO log_weight(goods_sn, which_col, old_value, new_value, update_user) SELECT goods_sn,'actual_weight',actual_weight,new_actual_weight,create_user FROM temp_170830;本来以为到这里就已经大功告成了，毕竟只是插入一些日志记录，后来为了简单的进行核对，发现数据有些不对劲，如下图所示：临时表数据截图：日志表数据截图：对比可以发现，插入的日志记录数据无缘无故后面多了很多位的小数，不知道从哪里冒出来的，后来一想，可能是本来浮点型的数据就是除不尽的，转成varchar的时候就把后面的那些也给带出来了，暂时也不是很确定，后续确定之后再补充，然后自己临时找了一个转varchar的方法concat，调整如下:INSERT INTO log_weight(goods_sn, which_col, old_value, new_value, update_user) SELECT goods_sn,'actual_weight',concat(actual_weight,''),concat(new_actual_weight,''),create_user FROM temp_170830;顺利解决日志记录问题。总结如下：1 在记录价格和重量数字字段的时候，尽量不要使用浮点型！！！，浮点数坑多（比如浮点型是不能判断相等的！！！），最好是采用int整型，业务上要显示小数时，读取出来再除以相应的位数，比如99.98元，应存储9998，读取出来时，用9998/100来显示。2 在float转varchar时，应该先把float用concat函数先转成varchar，再存储入varchar字段。

概念介绍：我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo日志是为了将数据恢复到修改之前的样子，因此在对数据库进行修改的时候，我们需要知道，这个过程中会产生redo日志和undo日志。存储位置：我们还知道，redo日志一般情况下放在redo日志文件中，也就是常说的ib_log中，而undo日志存放在数据库内部的一个"段"中，这个概念，我们在8月21号的文章中有讲过，忘记的同学可以回去看看，undo日志的段位于共享表空间内。回滚操作：现在，我们已经知道了undo的概念，其实就是共享表空间中的一块区域，它的主要作用是将事务恢复到执行修改之前的样子，但是，恢复的情况一般分为两种，一种是逻辑恢复，一种是物理恢复，这里需要非常强调的是，undo的恢复是逻辑恢复，也就是说，如果你插入了100w条数据，导致innodb分配了一个新的数据页来存储这些数据，那么在事务进行回滚的时候，undo的功能并不是回收这个数据页，而是将这些insert的操作，改变成delete的操作从而执行回滚。在这个过程中，共享表空间的大小并不会发生改变。除此之外，undo日志会将delete操作转化为insert操作，update操作转化为反向的update操作。删除方式：还有一点需要注意，事务共享表空间中写入undo日志的过程同样需要写入redo日志，事务一旦提交，也就意味着事务的持久性生效，那么undo日志则不被需要，但是innodb并不会把这个undo日志直接删除，而是放在一个undo日志的链表中，到底什么时候删除取决于mysql的purge线程，这样做是为了避免其他的事务需要通过undo日志来得到这条记录之前的版本。空间分配：在实际操作中，一个数据库实例上可能会进行很多事务，如果我们为每一个事务都分配单独的日志数据页来保存undo将会非常的浪费存储空间，我们简单算一算，假设一个应用的TPS为1000，为每个事务分配一个undo页，我们之后到一个数据页的大小是16kb，1分钟将会产生60*1000个数据页，那么一分钟大约需要的空间就是960M的磁盘空间，这样显然是不合理的，因此，在innodb中，对于undo页可以进行重用，具体的方法是，事务提交的时候，现将undo页放入链表中，然后判断这个undo页的使用空间是否小于75%,如果是的话，那么这个undo页就可以被重用，之后的undo日志就可以追加在当前undo日志的后面。当然，我们可以通过show engine innodb status来查看链表中undo log 的数量，这里不做演示了。

SQL 语句的大小写规则与语句组成元素、引用内容和服务器所使用的操作系统有关。1) SQL 关键字和函数名SQL 的关键字和函数名不区分大小写。例如，下面这些语句都是等价的：SELECT NOW();select now();sElEcT nOw();2) 数据库名、表名和视图名MySQL 用服务器主机的底层文件系统所包含的目录和文件来表示数据库和表。因此，数据库名和表名的默认大小写取决于服务器主机的操作系统在命名方面的规定。比如 Windows 系统的文件名不区分大小写，所以运行在 Windows 系统上面的 MySQL 服务器也不用区分数据库名和表名的大小写。Linux 系统的文件名区分大小写，所以运行在 Linux 系统上的 MySQL 服务器需要区分数据库名和表名的大小写。对于 Mac OS X 平台，其文件系统中的名字是个例外，它们不区分大小写。MySQL 会使用一个文件来表示一个视图，所以以上与表有关的也同样适用于视图。3) 存储程序的名字存储函数、存储过程和事件的名字都不区分大小写。触发器的名字要区分大小写，这一点与标准 SQL 的行为有所不同。4) 列名和索引名在 MySQL 里，列名和索引名都不区分大小写。例如，下面这些语句都是等价的：SELECT name FROM student ;SELECT NAME FROM student ;SELECT nAmE FROM student ;5) 别名的名字默认情况下，表的别名要区分大小写。SQL 语句中可以使用任意的大小写（大写、小写或大小写混用）来指定一个别名。如果需要在同一条语句里多次用到同一个别名，则必须让它们的大小写保持一致。 下表总结了 SQL 元素在 Windows 和 Linux 系统是否区分大小写。 WindowsLinux数据库名否（忽略大小写）是表名是表别名是列名否（忽略大小写）列别名否（忽略大小写）变量名是在 Linux 服务器下创建数据库和表时，应该认真考虑大小写的问题，比如它们以后是否会迁移到 Windows 服务器上。假设你在 Linux 服务器上创建了 abc 和 ABC 两个表，当把这两个表迁移到 Windows 服务器上时，就会出现问题；因为 Windows 系统并不区分大小写，abc 和 ABC 无差别。如果把表从 Linux 服务器迁移到 Windows 服务器时，也同样会遇到问题。想要避免大小写问题，可以先选定一种大小写方案，然后一直按照该方案去创建数据库和表。在阿里巴巴 Java 开发手册的 MySql 建表规约里提到：【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。通俗的说就是，MySQL 在 Windows 系统下不区分大小写，但在 Linux 系统下默认区分大小写。因此，数据库名、表名和字段名，都不允许出现任何大写字母，避免节外生枝。 一般建议统一使用小写字母，并且 InnoDB 引擎在其内部都是以小写字母方式来存储数据库名和表名的。这样可以有效的防止 MySQL 产生大小写问题。