【MySQL】如何用分页来查询 MySQL百万数据 才是最快的 ？ 在开发过程中我们经常会使用分页，核心技术是使用limit进行数据的读取，在使用limit进行分页的测试过程中，得到以下数据：select * from news order by id desc limit 0,10 耗时0.003秒 select * from news order by id desc limit 10000,10 耗时0.058秒 select * from news order by id desc limit 100000,10 耗时0.575秒 select * from news order by id desc limit 1000000,10 耗时7.28秒我们惊讶的发现mysql在数据量大的情况下分页起点越大查询速度越慢，100万条起的查询速度已经需要7秒钟。这是一个我们无法接受的数值！改进方案 1select * from news where id > (select id from news order by id desc limit 1000000, 1) order by id desc limit 0,10查询时间 0.365秒，提升效率是非常明显的！！原理是什么呢？？？我们使用条件对id进行了筛选，在子查询 (select id from news order by id desc limit 1000000, 1) 中我们只查询了id这一个字段比起select * 或 select 多个字段 节省了大量的查询开销！改进方案2 适合id连续的系统，速度极快！select * from news where id between 1000000 and 1000010 order by id desc不适合带有条件的、id不连续的查询。速度非常快！百万数据分页的注意事项 接上一节，我们加上查询条件：select id from news where cate = 1 order by id desc limit 500000 ,10查询时间 20 秒好恐怖的速度！！利用上面方案进行优化：select * from news where cate = 1 and id > (select id from news where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10 查询时间 15 秒优化效果不明显，条件带来的影响还是很大！在这样的情况下无论我们怎么去优化sql语句就无法解决运行效率问题。那么换个思路：建立一个索引表，只记录文章的id、分类信息，我们将文章内容这个大字段分割出去。表 news2 [ 文章表 引擎 myisam 字符集 utf-8 ]id int 11 主键自动增加cate int 11 索引在写入数据时将2张表同步，查询是则可以使用news2 来进行条件查询：select * from news where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10注意条件 id > 后面使用了news2 这张表！运行时间 1.23秒，我们可以看到运行时间缩减了近20倍！！数据在10万左右是查询时间可以保持在0.5秒左右，是一个逐步接近我们能够容忍的值！但是1秒对于服务器来说依然是一个不能接受的值！！还有什么可以优化的办法吗？？我们尝试了一个伟大的变化：将 news2 的存储引擎改变为innodb，执行结果是惊人的！select * from news where cate = 1 and id > (select id from news2 where cate = 1 order by id desc limit 500000,1 ) order by id desc limit 0,10只需要 0.2秒，非常棒的速度。到了这一步，我们的分页优化完毕，显然是有很大的效果的。你自己可以测试一下！

【MySQL】MySQL数据恢复 第一步：保证mysql已经开启binlog，查看命令：查看binklog是否开启 show variables like '%log_bin%'; 查看binlog存放日志文件目录（如下图，博主binlog目录为/data/mysql）： show variables like '%datadir%'; 值为OFF，需开启，值为ON，已开启。如果没有开启binlog，也没有预先生成回滚SQL，那可能真的无法快速回滚了。对存放重要业务数据的MySQL，强烈建议开启binlog。第二步：进入binlog文件目录，找出日志文件第三步：切换到mysqlbinlog目录（当线上数据出现错误的时候首先可以询问具体操作人记录时间点，这个时候可以借助mysql自带的binlog解析工具mysqlbinlog，具体位置在mysql安装目录**/mysql/bin/下）第四步：通过mysqlbinlog工具命令查看数据库增删改查记录（必须切换到mysqlbinlog目录才有效）例子1：查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志，输入如下命令将数据写入到一个备用的txt文件中mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 > template_coupon_tb_product_category.txt例子2：查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志，并输出到屏幕上mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 |more例子3：查询2018-11-12 09:00:00到2018-11-13 20:00:00 数据库为 youxi 的操作日志，并且过滤出 只包括 template_coupon_tb_product_category 表数据的操作记录 ，输入如下命令将数据写入到一个备用的txt文件中mysqlbinlog --no-defaults --database=youxi --start-datetime="2018-11-12 09:00:00" --stop-datetime="2018-11-13 20:00:00" /data/mysql/mysql-bin.000015 | grep template_coupon_tb_product_category > template_coupon_tb_product_category.txtmysqlbinlog 命令的语法格式： mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名 -------------------------------------------------------- 常用参数选项解释： --start-position=875 起始pos点 --stop-position=954 结束pos点 --start-datetime="2016-9-25 22:01:08" 起始时间点 --stop-datetime="2019-9-25 22:09:46" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志) -------------------------------------------------------- 不常用选项： -u --user=name 连接到远程主机的用户名 -p --password[=name] 连接到远程主机的密码 -h --host=name 从远程主机上获取binlog日志 --read-from-remote-server 从某个MySQL服务器上读取binlog日志第五步：利用第四步输出的sql语句或者txt文本进行语句过滤，重新插入数据或更新数据

【MySQL】如何优化无索引的join 现在有张 user 表，这个 user 表很简单，一个主键 id，也就是我们的用户 id，还有个 name 字段，很明显就是用户的姓名。这时候还有一张 user_info 表，这个 user_info 表存的是用户的一些其他信息，有 user_id 代表用户的 id，还有个 account 代表用户的存款。遍历循环查询如果要查出所有用户的姓名和存款，我们可以这样查： data = select * from user; for (i=0;i<len(data);i++) { info = select account from user_info where user_id= data[i].user_id }这种方式最直观，先通过 user 表拿到所有的用户信息，然后根据连接键 user_id 去 user_info 表里查询对应的 account，这样就能得到想要的数据，但是这种方式几个问题：第一次全表扫描 user 表需要一次网络通信，假设 user 表的数据量是n。然后循环查询 user_info 表，这里需要 n 次网络通信因此一共需要 n+1 次网络通信，如果使用的是长连接，还能省去 3 次握手的时间，如果是短连接，整体的开销会更大。其次如果 user_id 没有索引，那么整体更伤，假设 user_info 一共有 m 条数据，那么扫描的次数是怎么算的呢？首先 user 表是全表扫，一共需要查询 n 次。由于 user_info 表的 user_id 没有索引，那么每次查询等于都是全表扫，总共需要 n*m 次。因此这种查询的方式一共需要扫描 n+n*m 次。当然一般不会出现 user_id 没有索引的情况，在 user_id 有索引的时候，可以根据索引快速定位到我们的目标数据，并不需要全表扫描，因此总共需要扫描的行数为 n+m 次。join 查询一般对于这种情况的查询，我们会用 join 来做，于是我们的 sql 或许如下： select a.name,b.account from user a left join user_info b on a.id=b.user_id首先从网络通信上来说，总体只需要一次通信，至于 MySQL 内部怎么处理数据，怎么把我们想要的数据返回回来是它内部的事。其次我们来看看这种 join 方式的原理：从 user 表扫描一条数据，然后去 user_info 表中匹配在连接键 user_id 有索引的情况下，可以利用索引快速匹配然后把 user 表中的 name 和 user_info 表中的 account 作为结果集的一部分返回回去重复 1-3 步骤，直至 user 表扫描完毕，数据全部返回。其中第三步骤，每次组合一条数据的时候，并不是立马返回给客户端，这样效率太低，其实是有缓冲区的，也就是先把数据放在缓冲区中，等缓冲区满了，一次性响应给客户端可以大大提升效率。从原理来看和上面的遍历查询差不多，主要不同的是，客户端不需要和服务端多次通信。join buffer (Block Nested Loop)以上说的还是连接键有索引的，我们来看看连接键没有索引的情况，这时候你通过 explain 来看 MySQL 的执行计划，你会发现其中 user_info 的 extra 字段中会提示这个Using where; Using join buffer (Block Nested Loop)这是什么意思呢？因为没有索引，所以每次去 user 表得到一条数据的时候，肯定是要再到 user_info 表做全表扫描，这个扫描的成本我们上面也提到了，就是 n+n*m=n(1+m)，因此这个时间复杂度是和 n 成正比的，这也是为什么我们一般推荐「小表驱动大表」的方式。但是如果我们按照这个方式来做 join，未免开销太大了，太耗时了，于是还是沿用老套路，也就是用个临时存储区，也就是 extra 中的 join buffer，有了这个 join buffer 后，首先会把 user 表的数据放进去，然后扫描 user_info 表，每扫描一行数据，就和 join buffer 中的每一行 user 数据匹配，如果匹配上了，也就是我们要的结果，因为 user_info 表有 m 条数据，因此需要判断 n*m 次，咦！这个也没减少呀，还是和上面的一样。其实不一样，这里的 m 条数据其实每次都是和内存中的 n 条数据做匹配的，并非磁盘，内存的速度不用多说。聪明的读者可能会发现，如果 user 表的数据很多，join buffer 能放得下吗？ +------------------+--------+ | Variable_name | Value | +------------------+--------+ | join_buffer_size | 262144 | +------------------+--------+buffer 默认是 256K，多的话确实放不下，放不下的话，怎么办？其实也很简单，分段放即可，当读 user 表的数据占满 buffer 的时候，就不放了，然后直接和 user_info 做匹配，逻辑还是同上，在 buffer 的数据处理完之后，就清空它，接着上次的位置继续读入数据，再次重复同样的逻辑，直至数据读完。虽说连接键没有索引的时候，会通过 join buffer 来优化速度，但是现实中，还是建议大家尽量要保证连接键有索引。

【MySQL】清空表id归零 truncate语句，是清空表中的内容，包括自增主键的信息。truncate表后，表的主键就会重新从1开始。 语法： TRUNCATE TABLE table1

【MySQL】查看mysql版本的四种方法 **1：在终端下：mysql -V。 以下是代码片段：** 　　[shengting@login ~]$ mysql -V 　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686) 　　**2：在mysql中：mysql> status;** 　　以下是代码片段： 　　mysql> status; 　　-------------- 　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686) 　　Connection id: 416 　　SSL: Not in use 　　Current pager: stdout 　　Using outfile: '' 　　Using delimiter: ; 　　Server version: 3.23.56-log 　　Protocol version: 10 　　Connection: Localhost via UNIX socket 　　Client characterset: latin1 　　Server characterset: latin1 　　UNIX socket: /tmp/mysql_3311.sock 　　Uptime: 62 days 21 hours 21 min 57 sec 　 　Threads: 1 Questions: 584402560 Slow queries: 424 Opens: 59664208 Flush tables: 1 Open tables: 64 Queries per second avg: 107.551 　　**3：在help里面查找** 　　以下是代码片段： 　　[shengting@login ~]$ mysql --help | grep Distrib 　　mysql Ver 14.7 Distrib 4.1.10a, for redhat-linux-gnu (i686) 　　**4：使用mysql的函数** 　　以下是代码片段： 　　mysql> select version(); 　　+-------------+ 　　| version() | 　　+-------------+ 　　| 3.23.56-log | 　　+-------------+ 　　1 row in set (0.00 sec)

【MySQL】一个表的数据导入到另一个表 1.如果2张表的字段一致，并且希望插入全部数据，可以用这种方法： INSERT INTO 目标表 SELECT * FROM 来源表; insert into insertTest select * from insertTest2; 2.如果只希望导入指定字段，可以用这种方法： INSERT INTO 目标表 (`字段1`, `字段2`, ...) SELECT `字段1`, `字段2`, ... FROM 来源表;(这里的话字段必须保持一致) insert into insertTest2(`id`) select `id` from insertTest2; 3.如果您需要只导入目标表中不存在的记录，可以使用这种方法： INSERT INTO 目标表 (`字段1`, `字段2`, ...) SELECT `字段1`, `字段2`, ... FROM 来源表 WHERE not exists (select * from 目标表 where 目标表.比较字段 = 来源表.比较字段); 1>.插入多条记录： insert into insertTest2 (`id`,`name`) select `id`,`name` from insertTest where not exists (select * from insertTest2 where insertTest2.`id`=insertTest.id); 2>.插入一条记录： insert into insertTest (`id`, `name`) SELECT 100, 'liudehua' FROM dual WHERE not exists (select * from insertTest where insertTest.`id` = 100);

【MySQL】使用MySQL，请用好 JSON 这张牌！ 关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能，这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。 当然，很多同学在用 JSON 数据类型时会遇到各种各样的问题，其中最容易犯的误区就是将类型 JSON 简单理解成字符串类型。但当你看完这篇文章后，会真正认识到 JSON 数据类型的威力，从而在实际工作中更好地存储非结构化的数据。 ## JSON 数据类型 ## JSON（JavaScript Object Notation）主要用于互联网应用服务之间的数据交换。MySQL 支持RFC 7159定义的 JSON 规范，主要有 JSON 对象 和 JSON 数组 两种类型。下面就是 JSON 对象，主要用来存储图片的相关信息： { "Image": { "Width": 800, "Height": 600, "Title": "View from 15th Floor", "Thumbnail": { "Url": "http://www.example.com/image/xx9943", "Height": 125, "Width": 100 }, "IDs": [116, 943, 234, 38793] } } 从中你可以看到， JSON 类型可以很好地描述数据的相关内容，比如这张图片的宽度、高度、标题等（这里使用到的类型有整型、字符串类型）。 JSON对象除了支持字符串、整型、日期类型，JSON 内嵌的字段也支持数组类型，如上代码中的 IDs 字段。 另一种 JSON 数据类型是数组类型，如： [ { "precision": "zip", "Latitude": 37.7668, "Longitude": -122.3959, "Address": "", "City": "SAN FRANCISCO", "State": "CA", "Zip": "94107", "Country": "US" }, { "precision": "zip", "Latitude": 37.371991, "Longitude": -122.026020, "Address": "", "City": "SUNNYVALE", "State": "CA", "Zip": "94085", "Country": "US" } ] 上面的示例演示的是一个 JSON 数组，其中有 2 个 JSON 对象。 到目前为止，可能很多同学会把 JSON 当作一个很大的字段串类型，从表面上来看，没有错。但本质上，JSON 是一种新的类型，有自己的存储格式，还能在每个对应的字段上创建索引，做特定的优化，这是传统字段串无法实现的。JSON 类型的另一个好处是无须预定义字段，字段可以无限扩展。而传统关系型数据库的列都需预先定义，想要扩展需要执行 ALTER TABLE … ADD COLUMN … 这样比较重的操作。 需要注意是，JSON 类型是从 MySQL 5.7 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果要在生产环境中使用 JSON 数据类型，强烈推荐使用 MySQL 8.0 版本。 讲到这儿，你已经对 JSON 类型的基本概念有所了解了，接下来，我们进入实战环节：如何在业务中用好JSON类型？ ## 业务表结构设计实战 ## 用户登录设计 在数据库中，JSON 类型比较适合存储一些修改较少、相对静态的数据，比如用户登录信息的存储如下： DROP TABLE IF EXISTS UserLogin; CREATE TABLE UserLogin ( userId BIGINT NOT NULL, loginInfo JSON, PRIMARY KEY(userId) ); 由于当前业务的登录方式越来越多样化，如同一账户支持手机、微信、QQ 账号登录，所以这里可以用 JSON 类型存储登录的信息。 接着，插入下面的数据： SET @a = ' { "cellphone" : "1", "wxchat" : "码农", "77" : "1" } '; INSERT INTO UserLogin VALUES (1,@a); SET @b = ' { "cellphone" : "1188" } '; INSERT INTO UserLogin VALUES (2,@b); 从上面的例子中可以看到，用户 1 登录有三种方式：手机验证码登录、微信登录、QQ 登录，而用户 2 只有手机验证码登录。 而如果不采用 JSON 数据类型，就要用下面的方式建表： SELECT userId, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone, JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat FROM UserLogin; +--------+-------------+--------------+ | userId | cellphone | wxchat | +--------+-------------+--------------+ | 1 | 11| 码农 | | 2 | 11| NULL | +--------+-------------+--------------+ 2 rows in set (0.01 sec) 当然了，每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦，MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样： SELECT userId, loginInfo->>"$.cellphone" cellphone, loginInfo->>"$.wxchat" wxchat FROM UserLogin; 当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的 函数索引 功能对 JSON 中的某个字段进行索引。 比如在上面的用户登录示例中，假设用户必须绑定唯一手机号，且希望未来能用手机号码进行用户检索时，可以创建下面的索引： ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"); ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone); 上述 SQL 首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>"$.cellphone" 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询，就可以看到优化器会使用到新创建的 idx_cellphone 索引： EXPLAIN SELECT * FROM UserLogin WHERE cellphone = '11'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserLogin partitions: NULL type: const possible_keys: idx_cellphone key: idx_cellphone key_len: 1023 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 当然，我们可以在一开始创建表的时候，就完成虚拟列及函数索引的创建。如下表创建的列 cellphone 对应的就是 JSON 中的内容，是个虚拟列；uk_idx_cellphone 就是在虚拟列 cellphone 上所创建的索引。 CREATE TABLE UserLogin ( userId BIGINT, loginInfo JSON, cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"), PRIMARY KEY(userId), UNIQUE KEY uk_idx_cellphone(cellphone) ); 用户画像设计 某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。比如： 在电商行业中，根据用户的穿搭喜好，推荐相应的商品； 在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲； 在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。 在这，我强烈推荐你用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。假设有张画像定义表： CREATE TABLE Tags ( tagId bigint auto_increment, tagName varchar(255) NOT NULL, primary key(tagId) ); SELECT * FROM Tags; +-------+--------------+ | tagId | tagName | +-------+--------------+ | 1 | 70后 | | 2 | 80后 | | 3 | 90后 | | 4 | 00后 | | 5 | 爱运动 | | 6 | 高学历 | | 7 | 小资 | | 8 | 有房 | | 9 | 有车 | | 10 | 常看电影 | | 11 | 爱网购 | | 12 | 爱外卖 | +-------+--------------+ 可以看到，表 Tags 是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户 David，他的标签是 80 后、高学历、小资、有房、常看电影；用户 Tom，90 后、常看电影、爱外卖。 若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签： +-------+---------------------------------------+ |用户 |标签 | +-------+---------------------------------------+ |David |80后 ； 高学历 ； 小资 ； 有房 ；常看电影 | |Tom |90后 ；常看电影 ； 爱外卖 | +-------+--------------------------------------- 这样做的缺点是：不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。 用 JSON 数据类型就能很好解决这个问题： DROP TABLE IF EXISTS UserTag; CREATE TABLE UserTag ( userId bigint NOT NULL, userTags JSON, PRIMARY KEY (userId) ); INSERT INTO UserTag VALUES (1,'[2,6,8,10]'); INSERT INTO UserTag VALUES (2,'[3,10,12]'); 其中，userTags 存储的标签就是表 Tags 已定义的那些标签值，只是使用 JSON 数组类型进行存储。 MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes： ALTER TABLE UserTag ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array))); 如果想要查询用户画像为常看电影的用户，可以使用函数 MEMBER OF： EXPLAIN SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: ref possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: const rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$"); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | | 2 | [3, 10, 12] | +--------+---------------+ 2 rows in set (0.00 sec) 如果想要查询画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS： EXPLAIN SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 3 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]'); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | +--------+---------------+ 1 row in set (0.00 sec) 如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP： EXPLAIN SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: UserTag partitions: NULL type: range possible_keys: idx_user_tags key: idx_user_tags key_len: 9 ref: NULL rows: 4 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec) SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]'); +--------+---------------+ | userId | userTags | +--------+---------------+ | 1 | [2, 6, 8, 10] | | 2 | [3, 10, 12] | +--------+---------------+ 2 rows in set (0.01 sec) JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，我总结下今天的重点内容： 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes； JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性； 不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据； JSON 数据类型推荐使用在不经常更新的静态数据存储。 原文链接：https://blog.csdn.net/java_pfx/article/details/116594654

【MySQL】Mysql百万级数据迁移 mysqldump迁移平常开发中，我们比较经常使用的数据备份迁移方式是用mysqldump工具导出一个sql文件，再在新数据库中导入sql来完成数据迁移。试验发现，通过mysqldump导出百万级量的数据库成一个sql文件，大概耗时几分钟，导出的sql文件大小在1G左右，然后再把这个1G的sql文件通过scp命令复制到另一台服务器，大概也需要耗时几分钟。在新服务器的数据库中通过source命令来导入数据，我跑了一晚上都没有把数据导入进来，cpu跑满。脚本迁移直接通过命令行操作数据库进行数据的导出和导入是比较便捷的方式，但是数据量较大的情况下往往会比较耗时，对服务器性能要求也比较高。如果对数据迁移时间要求不是很高，可以尝试写脚本来迁移数据。虽然没有实际尝试，但是我想过大概有两种脚本方案。第一种方式，在迁移目标服务器跑一个迁移脚本，远程连接源数据服务器的数据库，通过设置查询条件，分块读取源数据，并在读取完之后写入目标数据库。这种迁移方式效率可能会比较低，数据导出和导入相当于是一个同步的过程，需要等到读取完了才能写入。如果查询条件设计得合理，也可以通过多线程的方式启动多个迁移脚本，达到并行迁移的效果。第二种方式，可以结合redis搭建一个“生产+消费”的迁移方案。源数据服务器可以作为数据生产者，在源数据服务器上跑一个多线程脚本，并行读取数据库里面的数据，并把数据写入到redis队列。目标服务器作为一个消费者，在目标服务器上也跑一个多线程脚本，远程连接redis，并行读取redis队列里面的数据，并把读取到的数据写入到目标数据库。这种方式相对于第一种方式，是一种异步方案，数据导入和数据导出可以同时进行，通过redis做数据的中转站，效率会有较大的提升。这里你也可以使用go语言来写迁移脚本，利用其原生的并发特性，可以达到并行迁移数据的目的，提升迁移效率。文件迁移第一种迁移方案效率太低，第二种迁移方案编码代价较高，通过对比和在网上找的资料分析，我最终选择了通过mysql的。 select data into outfile file.txt、load data infile file.txt into table的命令，以导入导出文件的形式完成了百万级数据的迁移。迁移过程在源数据库中导出数据文件 select * from dc_mp_fans into outfile '/data/fans.txt'; 复制数据文件到目标服务器 zip fans.zip /data/fans.txtscp fans.zip root@ip:/data/ 在目标数据库导入文件 unzip /data/fans.zip load data infile '/data/fans.txt' into table wxa_fans(id,appid,openid,unionid,@dummy,created_at,@dummy,nickname,gender,avatar_url,@dummy,@dummy,@dummy,@dummy,language,country,province,city,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy,@dummy);按照这么几个步骤操作，几分钟内就完成了一个百万级数据表的跨服务器迁移工作。注意项mysql安全项设置在mysql执行load data infile和into outfile命令都需要在mysql开启了secure_file_priv选项， 可以通过show global variables like '%secure%';查看mysql是否开启了此选项，默认值Null标识不允许执行导入导出命令。通过vim /etc/my.cnf修改mysql配置项，将secure_file_priv的值设置为空： [mysqld] secure_file_priv=''则可通过命令导入导出数据文件。导入导出的数据表字段不对应上面示例的从源数据库的dc_mp_fans表迁移数据到目标数据库的wxa_fans表，两个数据表的字段分别为：在导入数据的时候，可以通过设置字段名来匹配目标字段的数据，可以通过@dummy丢弃掉不需要的目标字段数据。总结结合本次数据迁移经历，总结起来就是：小数据量可以使用mysqldump命令进行导入导出，这种方式简单便捷。数据量较大，且有足够的迁移耐心时，可以选择自己写脚本，选择合适的并行方案迁移数据，这种方式编码成本较高。数据量较大，且希望能在短时间内完成数据迁移时，可以通过mysql导入导出文件的方式来迁移，这种方式效率较高。

【MySQL】MyISAM与InnoDB区别 在MySQL数据库中，有四种存储引擎：MEMORY存储引擎、MERGE存储引擎、MyIASM存储引擎、Innodb存储引擎。常用的引擎主要就是2个：Innodb和MyIASM。这里就只讨论Innodb和MyIASM的区别。1、 构造上的区别MyISAM在磁盘上存储成三个文件，其中.frm文件存储表定义；.MYD (MYData)为数据文件；.MYI (MYIndex)为索引文件。innodb是由.frm文件、表空间（分为独立表空间或者共享表空间）和日志文件（redo log）组成。2、事务上的区别myisam不支持事务；而innodb支持事务3、锁上的区别myisam使用的是表锁；而innodb使用的行锁（当然innodb也支持表锁）。表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许，因此myisam支持的并发量低，但myisam不会出现死锁；行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。因此行锁能大大的减少数据库操作的冲突，但有时会导致死锁。4、是否支持外键的区别myisam不支持外键，innodb支持外键5、表的具体行数的区别MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。6、 存储空间上的区别MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

【MySQL】配置MySQL主从数据库 MySQL的主从复制是通过binlog日志来实现的，主从复制中的“主”指的是MySQL主服务器上的数据库，“从”指的是MySQL从服务器上的数据库，且这种复制是基于数据库级别的，为此从服务器中的数据库名称必须和主服务器中的数据库名称保持一致，那么，要想实现主从复制，我们至少要有两个MySQL服务器（最好是两个MySQL服务器分别位于不同的主机上，或者在一个主机上安装两个MySQL，端口不同即可）。这里我们只介绍 一主一从 的操作方法。 我的电脑已经安装了 phpStudy 集成环境（同wamp/xampp安装包类似），可以把它里面的MySQL服务作为MySQL的主服务器。那么，我们还需要在这台电脑上再安装一个MySQL，作为数据库的从服务器。 我的电脑phpStudy 中已安装的MySQL版本为 5.6.20，端口为3306。我这里又新建了一个mysql 服务（注意：这个要一个免安装版，而且要比住数据库版本要低才好！） 下载mysql的windows安装包 到mysql的官网：http://dev.mysql.com/downloads/mysql/5.6.html#downloads 进行下载 注意：官网给出的安装包有两种格式（msi格式和 zip格式），如果下载的是msi格式的，则直接双击安装即可，这里我们下载zip格式的。 数据库服务器的参数：主服务器（master）： IP为127.0.0.1，端口为3306从服务器（slave）： IP为127.0.0.1，端口为3307主服务器配置：修改主服务器的数据库配置文件（E:\xampp\mysql\bin\my.ini），在 [mysqld] 标签的最下面，添加如下代码： 需要备份的数据库 `binlog-do-db=test` 不需要备份的数据库 `binlog-ignore-db=mysql` 开启二进制日志 `log-bin=mysql-bin` 服务器id `server-id=1` 保存退出，重启MySQL主服务器。 binlog-do-db用于指定需要同步的数据库，binlog-ignore-db指定不需要同步的数据库，如果这两个参数都不设置，则从服务器会复制主服务器的所有数据库。一般不用root账号作同步账号，为此，我们需要在主服务器上创建一个新的用户（如 user01，密码为123456）。 这里我们用命令行的方式创建，方法如下：打开cmd，切换至 E:\xampp\mysql\bin，用 root 账户连接MySQL主服务器： `mysql -uroot -p -P3306 ` 创建新用户： `create user ‘user01’@’127.0.0.1’ identified by ‘123456’; ` （@后面的ip地址为允许连接的客户端的ip地址。） 然后，给新用户配置主从复制的权限： `grant replication slave on . to ‘user01’@’127.0.0.1’ identified by ‘123456’; ` （@后面的ip地址为允许连接的客户端的ip地址，如果改为 ‘%’，就表示客户端没有ip地址的限制）如果主服务器的数据库（test）中，已经有数据，我们需要先手动把主服务器中的数据复制到从服务器。方法如下： 在本案例中，我们只备份一个数据库（test），test中有一个表basic_user，表中也已经有了数据。为了防止我们复制数据的时候，数据库test中的数据发生更新，我们需要先锁定数据库，命令如下： `flush tables with read lock; ` 这个命令是全局读锁定，它会给主服务器中的所有数据库都加上读锁，这里顺便说一下读锁和写锁的区别：read lock（读锁）：也叫共享锁，允许所有的读操作，但阻塞写操作，即所有连接只可以读数据，但不允许写数据。write lock（写锁）：也叫排它锁、独占锁，只允许当前连接的读和写，不允许其他并发的读操作和写操作。锁定主服务器的数据库后，我们在从服务器中，也创建一个数据库test，并将所有的表（包括表结构和表数据）都导入。 然后，我们执行下面的命令，解锁：unlock tables; 查看主服务器的 master 状态： mysql> show master status; +——————+———-+————–+——————+——————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +——————+———-+————–+——————+——————-+ | mysql-bin.000008 | 498 | test | mysql | | +——————+———-+————–+——————+——————-+从服务器配置：修改从服务器的数据库配置文件（E:\mysql\my.ini），在 [mysqld] 标签的最下面，添加如下代码： 端口 port = 3307 服务器id server_id = 2 开启二进制日志（从服务器不是必须要开启二进制日志） log-bin=mysql-bin 保存退出，重启MySQL服务。 连接MySQL从服务器： mysql -uroot -p -P3307 配置复制的参数： change master to master_host='127.0.0.1',master_user='root',master_password='root',master_log_file='binlog.000009',master_log_pos=154;参数详解：master_host： 主服务器的IP master_user： 主服务器上新创建的用户名 master_password：用户的密码 master_port： 主服务器的端口，如果未曾修改，默认即可。 master_log_file：主服务器二进制日志文件的名称，填写查看主服务器的master状态时显示的File的值 master_log_pos：日志的位置，填写查看主服务器的master状态时显示的Position的值启动从服务器的slave复制功能：start slave; 查看从服务器的slave状态： mysql> show slave status \G ***************** 1. row ***************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: user01 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 120 Relay_Log_File: hp-PC-relay-bin.000004 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000009 Slave_IO_Running: Yes Slave_SQL_Running: Yes如果 Slave_IO_Running 和 Slave_SQL_Running 的值都为 Yes，则说明主从复制的所有配置已成功，即从服务器已经可以自动与主服务器的数据库数据实现同步了。此后，只要主服务器的数据有更新（比如：在 test数据库中新建了一张表或者表中的数据发生了变化），从服务器都会自动与主服务器保持一致。但如果有人刻意改变了从服务器的数据，主服务器中的数据并不会同步更新，除非我们把这两个MySQL服务器设置为互为主从。

【MySQL】MySQL触发器应用场景和使用方法 触发器是一种特殊的存储过程，它在插入，删除或改动特定表中的数据时触发运行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。数据库触发器有下面的作用：1.安全性。能够基于数据库的值使用户具有操作数据库的某种权利。# 能够基于时间限制用户的操作，比如不同意下班后和节假日改动数据库数据。# 能够基于数据库中的数据限制用户的操作，比如不同意股票的价格的升幅一次超过10%。2.审计。能够跟踪用户对数据库的操作。 # 审计用户操作数据库的语句。# 把用户对数据库的更新写入审计表。3.实现复杂的数据完整性规则# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器能够引用列或数据库对象。比如，触发器可回退不论什么企图吃进超过自己保证金的期货。# 提供可变的缺省值。4.实现复杂的非标准的数据库相关完整性规则。触发器能够对数据库中相关的表进行连环更新。比如，在auths表author_code列上的删除触发器可导致对应删除在其他表中的与之匹配的行。# 在改动或删除时级联改动或删除其他表中的与之匹配的行。# 在改动或删除时把其他表中的与之匹配的行设成NULL值。# 在改动或删除时把其他表中的与之匹配的行级联设成缺省值。# 触发器可以拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这样的触发器会起作用。比如，可以在books.author_code 列上生成一个插入触发器，假设新值与auths.author_code列中的某值不匹配时，插入被回退。5.同步实时地复制表中的数据。6.自己主动计算数据值，假设数据的值达到了一定的要求，则进行特定的处理。比如，假设公司的帐号上的资金低于5万元则马上给財务人员发送警告数据。mysql创建触发器1、老版本mysql语法：CREATE TRIGGER <触发器名称> --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.{ BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。<触发器SQL语句> --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。2、新版本mysql语法： CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name注意：或许你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER），如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。mysql5.7新版本语法发生了一些小小的变化，同时也可以自定义用户，语法相对来说简单了很多，原理和老版本还是一样的；准备工作：1、创建表tab1sql命令： DROP TABLE IF EXISTS tab1; CREATE TABLE tab1( tab1_id varchar(11) );2、创建表tab2sql命令： DROP TABLE IF EXISTS tab2; CREATE TABLE tab2( tab2_id varchar(11) );实例1：1、创建触发器：t_afterinsert_on_tab1作用：增加tab1表记录后自动将记录增加到tab2表中老版本sql命令： DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW BEGIN insert into tab2(tab2_id) values(new.tab1_id); END;新版本sql命令： DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; CREATE TRIGGER t_afterinsert_on_tab1 AFTER INSERT ON tab1 FOR EACH ROW insert into tab2(tab2_id) values(new.tab1_id);2、向tab1表插入一条数据做测试；sql命令： INSERT INTO tab1(tab1_id) values('0001'); 3、查看tab1表和tab2表的结果；sql命令： SELECT * FROM tab1; SELECT * FROM tab2;实例2：1、创建触发器：t_afterdelete_on_tab1作用：删除tab1表记录后自动将tab2表中对应的记录删去老版本sql命令： DROP TRIGGER IF EXISTS t_afterdelete_on_tab1; CREATE TRIGGER t_afterdelete_on_tab1 AFTER DELETE ON tab1 FOR EACH ROW BEGIN delete from tab2 where tab2_id=old.tab1_id; END;新版本sql命令： DROP TRIGGER IF EXISTS t_afterdelete_on_tab1; CREATE TRIGGER t_afterdelete_on_tab1 AFTER DELETE ON tab1 FOR EACH ROW delete from tab2 where tab2_id=old.tab1_id;2、删除tab1表一条数据sql命令： `DELETE FROM tab1 WHERE tab1_id='0001';` 3、查看tab1表和tab2表的结果sql命令： SELECT * FROM tab1; SELECT * FROM tab2;三、mysql查看触发器和查看数据库（show databases;）查看表格（show tables;）一样，查看触发器的语法如下： `SHOW TRIGGERS [FROM schema_name];` 其中，schema_name 即 Schema 的名称，在 MySQL 中 Schema 和 Database 是一样的，也就是说，可以指定数据库名，这样就不必先“USE database_name;”了。 实例：1、查看tab1表下面所有的触发器：sql命令： `SHOW TRIGGERS` 2、查看test数据库下面的触发器，tabl表属于test数据库：sql命令： `SHOW TRIGGERS FROM test` 四、mysql删除触发器和删除数据库、删除表格一样，删除触发器的语法如下： `DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name` 实例：1、删除触发器‘t_afterdelete_on_tab1’sql命令： `DROP TRIGGER IF EXISTS t_afterdelete_on_tab1` 参考：付杰博客 https://www.fujieace.com/mysql/trigger.html

【MySQL】MySQL 事务 MySQL 事务MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。事务用来管理 insert,update,delete 语句一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。事务控制语句：BEGIN 或 START TRANSACTION 显式地开启一个事务；COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；ROLLBACK TO identifier 把事务回滚到标记点；SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。MYSQL 事务处理主要有两种方法：1、用 BEGIN, ROLLBACK, COMMIT来实现BEGIN 开始一个事务ROLLBACK 事务回滚COMMIT 事务确认2、直接用 SET 来改变 MySQL 的自动提交模式:SET AUTOCOMMIT=0 禁止自动提交SET AUTOCOMMIT=1 开启自动提交事务测试mysql> use RUNOOB; Database changed mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表 Query OK, 0 rows affected (0.04 sec) mysql> select * from runoob_transaction_test; Empty set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test value(5); Query OK, 1 rows affected (0.01 sec) mysql> insert into runoob_transaction_test value(6); Query OK, 1 rows affected (0.00 sec) mysql> commit; # 提交事务 Query OK, 0 rows affected (0.01 sec) mysql> select * from runoob_transaction_test; +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql> begin; # 开始事务 Query OK, 0 rows affected (0.00 sec) mysql> insert into runoob_transaction_test values(7); Query OK, 1 rows affected (0.00 sec) mysql> rollback; # 回滚 Query OK, 0 rows affected (0.00 sec) mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入 +------+ | id | +------+ | 5 | | 6 | +------+ 2 rows in set (0.01 sec) mysql>PHP中使用事务实例MySQL ORDER BY 测试：<?php $dbhost = 'localhost'; // mysql服务器主机地址 $dbuser = 'root'; // mysql用户名 $dbpass = '123456'; // mysql用户名密码 $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('连接失败: ' . mysqli_error($conn)); } // 设置编码，防止中文乱码 mysqli_query($conn, "set names utf8"); mysqli_select_db( $conn, 'RUNOOB' ); mysqli_query($conn, "SET AUTOCOMMIT=0"); // 设置为不自动提交，因为MYSQL默认立即执行 mysqli_begin_transaction($conn); // 开始事务定义 if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)")) { mysqli_query($conn, "ROLLBACK"); // 判断当执行失败时回滚 } if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)")) { mysqli_query($conn, "ROLLBACK"); // 判断执行失败时回滚 } mysqli_commit($conn); //执行事务 mysqli_close($conn); ?>

【MySQL】Cannot add foreign key constraint 错误解决办法 产生这个错误的多数原因有一下两点： 1，两张表里要设主键和外键的字段的数据类型或者数据长度不一样 （例如这个是int 另外一个是tinyint，或者都是int，但是设置的长度不同）2，某个表里已经有记录了3、两个表的引擎不一样，查看表的引擎语句：show table status from 数据库名 where name='表名';4、要设置外键的字段不能为主键5、改建所参考的字段必须为主键6、两个字段必须具有相同的数据类型和约束我遇到的情况就是4、5。后面关联的表的字段必须被设置为主键，才能关联成功。参考方案：https://blog.csdn.net/TCF_JingFeng/article/details/84332709参考方案：https://www.cnblogs.com/olddriver123/p/8335263.html

【MySQL】MySQL添加用户、删除用户与授权及基本操作 > MySql中添加用户,新建数据库,用户授权,删除用户,修改密码(注意每行后边都跟个;表示一个命令语句结束): **1.新建用户** 　　1.1 登录MYSQL： @>mysql -u root -p @>密码 　　1.2 创建用户： mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234")); 　　这样就创建了一个名为：test 密码为：1234 的用户。 > 注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。 　　1.3 然后登录一下： mysql>exit; 　　@>mysql -u test -p 　　@>输入密码 　　mysql>登录成功 **2.为用户授权** > 授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"; 　　2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录： @>mysql -u root -p @>密码 　　2.2 首先为用户创建一个数据库(testDB)： mysql>create database testDB; 　　2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）： mysql>grant all privileges on testDB.* to test@localhost identified by '1234'; mysql>flush privileges;//刷新系统权限表 > 格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码"; 　　2.4 如果想指定部分权限给一用户，可以这样来写: mysql>grant select,update on testDB.* to test@localhost identified by '1234'; mysql>flush privileges; //刷新系统权限表 　　2.5 授权test用户拥有所有数据库的某些权限： 　 　　 mysql>grant select,delete,update,create,drop on *.* to test@"%" identified by "1234"; > //test用户对所有数据库都有select,delete,update,create,drop 权限。 　 > //@"%"表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。） > //对localhost授权：加上一句grant all privileges on testDB.* to test@localhost identified by '1234';即可。 **3. 删除用户** @>mysql -u root -p @>密码 mysql>Delete FROM user Where User='test' and Host='localhost'; mysql>flush privileges; mysql>drop database testDB; //删除用户的数据库 删除账户及权限：>drop user 用户名@'%'; 　　　　　　　　>drop user 用户名@ localhost; **4. 修改指定用户密码** @>mysql -u root -p @>密码 mysql>update mysql.user set password=password('新密码') where User="test" and Host="localhost"; mysql>flush privileges; **5. 列出所有数据库** mysql>show database; **6. 切换数据库** mysql>use '数据库名'; **7. 列出所有表** mysql>show tables; **8. 显示数据表结构** mysql>describe 表名; **9. 删除数据库和数据表** mysql>drop database 数据库名; mysql>drop table 数据表名;

【MySQL】优化MySQL数据库的方法 1、开启慢查询日志，使用mysql慢查询日志对有效率问题的sql语句进行监控，定位执行较慢的查询语句，从而决定优化方案2、sql查询优化，如使用连接（JOIN）来代替子查询、优化group by查询等3、建立合适的索引4、适用外键，保证数据的关联性5、使用事务，事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。6、数据库表结构优化表的垂直拆分：把有很多列的表拆分成多个表，解决表的宽度问题。通常是按照功能进行拆分，可以按以下原则：不常用的字段单独存放一个表中，大字段独立存放在一张附表中。业务模块不明晰，耦合(表关联)度比较高的系统不适合这种拆分方式。表的水平拆分：当表的数据比较多的时候，可以选择将表进行水平拆分，水平拆分的本质并没有改变表的结构仅是将原本存放在同一个表中的数据放到了多个结构一样的表中。

【MySQL】MySQL命令总结 完整的创建数据库例子： >create database db_test default character set utf8 collate utf8_general_ci; >use db_test; >CREATE TABLE tb_user( >id INT(10) NOT NULL AUTO_INCREMENT COMMENT"主键", >name VARCHAR(20) NOT NULL COMMENT"姓名", >age INT(3) DEFAULT 0, >PRIMARY KEY(id) >) COMMENT"用户表"; 其他有关命令： 1,启动和关闭数据库 >net start mysql >net stop mysql 所以命令必须是一行的第一个，并且以分号结尾： All text commands must be first on line and end with ";" 2,根据用户名密码，登录数据库 >mysql -uroot -p; 如果数据库没有密码则使用 >mysql -uroot; 3,查看在当前服务器中有多少个数据库 >show databases; 4,删除某个数据库 >drop database databaseName; >commit; 5,创建数据库 >CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 更改数据库的字符编码 ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 6,选择使用某个数据库 >use databaseName; 7,查看数据库中有多少的表 >show tables; 8,创建表 >create table tableName( id int(10) NOT NULL AUTO_INCREMENT,PRIMARY KEY(id), name varchar(20) ); 9,显示本数据库的所有表 >show tables; 10,显示某一个表 >show create table tableName; 11,显示表结构 >describe tableName; (或者简写: desc tableName;) 12,向表中加入数据并查看 >insert into tableName(id,name...) values('1','admin',...); >select * from tableName; 13,导入.sql文件(文件所在路径是F:\file.sql >source F:/file.sql; 14,删除表 >drop table tableName; 15,删除表中的所有数据，但是表结构依然存在 >delete from tableName; 16,更新表中的数据,如果没有where，则将影响所有的记录 >update tableName set name='administrator' where id='1'; 17,查看服务器版本和当前日期 >select version(),current_date; >select version(); >select now(); 18,把mysql作为一个简单的计算器 select pi(); >select pi()*10; 19,查看用户 >select user(); 20,使用load >load data local infile filePath into table tableName; 21，慢日志 查看慢日志是否开启：show variables like '%slow_query_log'; 开启慢日志：set global slow_query_log=1; 查看慢查询时间：show variables like '%long_query_time%'; 修改慢查询时间：set global long_query_time=2;//两秒

【MySQL】MySQL中批量替换某个字段的部分数据 1.修改字段里的所有含有指定字符串的文字 UPDATE 表A SET 字段B = replace(字段B, 'aaa', 'bbb') example: update table set url= replace(url, 'aaa', 'bbb') 【将url字段中的aaa批量更改为bbb】 update table set url= REPLACE (url,'3','1.png') where 条件; 2.常规条件修改： update table set column='' where column is null

【MySQL】MySQL添加用户、删除用户与授权 MySql中添加用户,新建数据库,用户授权,删除用户,修改密码(注意每行后边都跟个;表示一个命令语句结束): 1.新建用户 　　1.1 登录MYSQL： 　　@>mysql -u root -p 　　@>密码 　　1.2 创建用户： 　　mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234")); 　　这样就创建了一个名为：test 密码为：1234 的用户。 　　注意：此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。 　　1.3 然后登录一下： 　　mysql>exit; 　　@>mysql -u test -p 　　@>输入密码 　　mysql>登录成功 2.为用户授权 　　授权格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　 　　2.1 登录MYSQL（有ROOT权限），这里以ROOT身份登录： 　　@>mysql -u root -p 　　@>密码 　　2.2 首先为用户创建一个数据库(testDB)： 　　mysql>create database testDB; 　　2.3 授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）： 　　 mysql>grant all privileges on testDB.* to test@localhost identified by '1234'; 　　mysql>flush privileges;//刷新系统权限表 　　格式：grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";　 　　2.4 如果想指定部分权限给一用户，可以这样来写: 　　mysql>grant select,update on testDB.* to test@localhost identified by '1234'; 　　mysql>flush privileges; //刷新系统权限表 　　2.5 授权test用户拥有所有数据库的某些权限： 　 　　mysql>grant select,delete,update,create,drop on *.* to test@"%" identified by "1234"; //test用户对所有数据库都有select,delete,update,create,drop 权限。 　 //@"%" 表示对所有非本地主机授权，不包括localhost。（localhost地址设为127.0.0.1，如果设为真实的本地地址，不知道是否可以，没有验证。） 　//对localhost授权：加上一句grant all privileges on testDB.* to test@localhost identified by '1234';即可。 3. 删除用户 　　@>mysql -u root -p 　　@>密码 　　mysql>Delete FROM user Where User='test' and Host='localhost'; 　　mysql>flush privileges; 　　mysql>drop database testDB; //删除用户的数据库 删除账户及权限：>drop user 用户名@'%'; 　　　　　　　　>drop user 用户名@ localhost; 4. 修改指定用户密码 　　@>mysql -u root -p 　　@>密码 　　mysql>update mysql.user set password=password('新密码') where User="test" and Host="localhost"; 　　mysql>flush privileges; 5. 列出所有数据库 　　mysql>show database; 6. 切换数据库 　　mysql>use '数据库名'; 7. 列出所有表 　　mysql>show tables; 8. 显示数据表结构 　　mysql>describe 表名; 9. 删除数据库和数据表 　　mysql>drop database 数据库名; 　　mysql>drop table 数据表名;

【MySQL】MySQL索引失效的几个情况 索引查询失效的几个情况：1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效3、组合索引，不是使用第一列索引，索引失效。4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。5、在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。（此处是错误的！）在索引列上使用 IS NULL 或 IS NOT NULL操作，索引不一定失效！！！6、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。7、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）8、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。索引失效分析工具：可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。