SQL的INTERSECT与MySQL模拟INTERSECT SQL的INTERSECT在SQL中，INTERSECT是对两个SQL语句的查询结果做与运算，即值同时存在于两个语句才被选出(交集)。select id from table1 -- 输出 id(1,2,3) intersect select id from table2 -- 输出 id(2,3,4) //得出 id(2,3)MySQL模拟INTERSECTMySQL中没有INTERSECT函数，我们可以使用INNER JOIN和DISTINCT来获取这两个结果集的交集。SELECT DISTINCT t1.value FROM table1 t1 INNER JOIN table2 t2 ON t1.value = t2.valueINTERSECT或join进行与运算条件列的顺序和数量必须相同。相应列的数据类型必须兼容或可转换。

mysql的join几个概念 内连接：内连接是根据两个表之间的匹配条件返回匹配行的结果集。它并不代表左右连接的并集，而是共同的部分（交集）。左连接与右连接：左连接（或左外连接）：左连接返回左表中所有的行，以及右表中与左表中行匹配的行。左连接的结果包括左表的所有行以及与左表匹配的右表行，若右表中没有与左表匹配的行，则在结果中对应的列包含NULL值。右连接（或右外连接）：右连接返回右表中所有的行，以及左表中与右表中行匹配的行。右连接的结果包括右表的所有行以及与右表匹配的左表行，若左表中没有与右表匹配的行，则在结果中对应的列包含NULL值。并集和交集：并集：并集是合并两个集合的操作，不涉及连接概念。在数据库中，并集一般不是连接的一部分。交集：交集是指两个集合共同拥有的元素的集合，对应于内连接的概念。MySQL、Oracle 和 SQL Server 中的连接：在 SQL 中，MySQL 支持所有类型的连接（包括内连接、左连接、右连接、全外连接），并不限于内连接。Oracle 和 SQL Server 也支持各种类型的连接，包括内连接、左连接、右连接和全外连接。

mysql复习 迭代学习法原文件下载布尔教育_Mysql基础_练习准备.sql复习秘籍.htmlmysql加强.pdf一:复习前的准备1:确认你已安装wamp2:确认你已安装ecshop,并且ecshop的数据库名为shop二 基础知识:1.数据库的连接mysql -u -p -h-u 用户名-p 密码-h host主机2:库级知识2.1 显示数据库: show databases;2.2 选择数据库: use dbname;2.3 创建数据库: create database dbname charset utf8;2.3 删除数据库: drop database dbname;3: 表级操作:3.1 显示库下面的表show tables;3.2 查看表的结构: desc tableName;3.3 查看表的创建过程: show create table tableName;3.4 创建表:　create table tbName (列名称1　列类型　[列参数]　[not null default ],....列2.......列名称N　列类型　[列参数]　[not null default ])engine myisam/innodb charset utf8/gbk3.4的例子:create table user (id int auto_increment, name varchar(20) not null default '', age tinyint unsigned not null default 0,index id (id) )engine=innodb charset=utf8;注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,charset 常用的有utf8,gbk;3.5 修改表3.5.1 修改表之增加列:alter table tbName add 列名称１　列类型　[列参数]　[not null default ]　#(add之后的旧列名之后的语法和创建表时的列声明一样)3.5.2 修改表之修改列alter table tbNamechange 旧列名 新列名 列类型　[列参数]　[not null default ](注:旧列名之后的语法和创建表时的列声明一样)3.5.3 修改表之减少列:alter table tbName drop 列名称;3.5.4 修改表之增加主键alter table tbName add primary key(主键所在列名);例:alter table goods add primary key(id)该例是把主键建立在id列上3.5.5 修改表之删除主键alter table tbName　drop primary key;3.5.6 修改表之增加索引alter table tbName add [unique|fulltext] index 索引名(列名);3.5.7 修改表之删除索引alter table tbName drop index 索引名;3.5.8 清空表的数据truncate tableName;4:列类型讲解列类型: 整型:tinyint (0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (参考手册11.2) 参数解释: unsigned 无符号(不能为负) zerofill 0填充 M 填充后的宽度 举例:tinyint unsigned; tinyint(6) zerofill; 数值型 浮点型:float double 格式:float(M,D) unsigned\zerofill; 字符型 char(m) 定长 varchar(m)变长 text 列 实存字符i 实占空间 利用率char(M) 0<=i<=M M i/m<=100%varchar(M) 0<=i<=M i+1,2 i/i+1/2<100% year YYYY 范围:1901~2155. 可输入值2位和4位(如98,2012)日期时间类型 date YYYY-MM-DD 如:2010-03-14 time HH:MM:SS 如:19:26:32 datetime YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32 timestamp YYYY-MM-DD HH:MM:SS 特性:不用赋值,该列会为自己赋当前的具体时间 5:增删改查基本操作5.1 插入数据insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列 insert into 表名 values (,,,,); -- 插入所有列 insert into 表名 values -- 一次插入多行 (val1,val2……), (val1,val2……), (val1,val2……); 5.3修改数据update tablename set col1=newval1, col2=newval2, ... ... colN=newvalN where 条件; 5.4，删除数据 delete from tablenaeme where 条件;5.5， select 查询（1） 条件查询 where a. 条件表达式的意义，表达式为真，则该行取出 b. 比较运算符 = ，!=，< > <= >= c. like , not like ('%'匹配任意多个字符,'_'匹配任意单个字符) in , not in , between and d. is null , is not null （2） 分组 group by 一般要配合5个聚合函数使用:max,min,sum,avg,count（3） 筛选 having （4） 排序 order by （5） 限制 limit6: 连接查询6.1， 左连接.. left join .. on table A left join table B on tableA.col1 = tableB.col2 ; 例句: select 列名 from table A left join table B on tableA.col1 = tableB.col2右链接: right join内连接: inner join左右连接都是以在左边的表的数据为准,沿着左表查右表.内连接是以两张表都有的共同部分数据为准,也就是左右连接的数据之交集.7 子查询where 型子查询:内层sql的返回值在where后作为条件表达式的一部分 例句: select * from tableA where colA = (select colB from tableB where ...);from 型子查询:内层sql查询结果,作为一张表,供外层的sql语句再次查询 例句:select from (select from ...) as tableName where ....8: 字符集客服端sql编码 character_set_client 服务器转化后的sql编码 character_set_connection 服务器返回给客户端的结果集编码 character_set_results 快速把以上3个变量设为相同值: set names 字符集存储引擎 engine=1\2 1 Myisam 速度快 不支持事务 回滚 2 Innodb 速度慢 支持事务,回滚①开启事务 start transaction ②运行sql; ③提交,同时生效\回滚 commit\rollback触发器 trigger 监视地点:表 监视行为:增 删 改 触发时间:after\before 触发事件:增 删 改创建触发器语法create trigger tgName after/before insert/delete/update on tableName for each row sql; -- 触发语句 删除触发器:drop trigger tgName;索引提高查询速度,但是降低了增删改的速度,所以使用索引时,要综合考虑. 索引不是越多越好,一般我们在常出现于条件表达式中的列加索引. 值越分散的列，索引的效果越好索引类型 primary key主键索引 index 普通索引 unique index 唯一性索引 fulltext index 全文索引综合练习:连接上数据库服务器创建一个gbk编码的数据库建立商品表和栏目表,字段如下:商品表:goodsgoods_id　--主键,goods_name -- 商品名称cat_id -- 栏目idbrand_id -- 品牌idgoods_sn -- 货号goods_number -- 库存量shop_price -- 价格goods_desc　--商品详细描述栏目表:categorycat_id --主键 cat_name -- 栏目名称parent_id -- 栏目的父id建表完成后,作以下操作:删除goods表的goods_desc 字段,及货号字段并增加字段:click_count -- 点击量在goods_name列上加唯一性索引在shop_price列上加普通索引在clcik_count列上加普通索引删除click_count列上的索引对goods表插入以下数据:goods_idgoods_namecat_idbrand_idgoods_sngoods_numbershop_priceclick_count1KD87648ECS000000101388.0074诺基亚N85原装充电器81ECS0000041758.0003诺基亚原装5800耳机81ECS0000022468.0035索爱原装M2卡读卡器117ECS000005820.0036胜创KINGMAX内存卡110ECS0000061542.0007诺基亚N85原装立体声耳机HS-8281ECS00000720100.0008飞利浦9@9v34ECS00000817399.0099诺基亚E6631ECS000009132298.002010索爱C702c37ECS00001071328.001111索爱C702c37ECS00001111300.00012摩托罗拉A81032ECS0000128983.001413诺基亚5320 XpressMusic31ECS00001381311.001314诺基亚5800XM41ECS00001442625.00615摩托罗拉A81032ECS0000153788.00816恒基伟业G101211ECS0000160823.33317夏新N735ECS00001712300.00218夏新T545ECS00001812878.00019三星SGH-F25836ECS0000190858.00720三星BC0136ECS00002013280.001421金立 A30310ECS000021402000.00422多普达Touch HD33ECS00002205999.001523诺基亚N9651ECS00002383700.001724P80639ECS0000241482000.003625小灵通/固话50元充值卡130ECS000025248.00026小灵通/固话20元充值卡130ECS000026219.00027联通100元充值卡150ECS000027295.00028联通50元充值卡150ECS000028045.00029移动100元充值卡140ECS000029090.00030移动20元充值卡140ECS000030918.00131摩托罗拉E832ECS00003111337.00532诺基亚N8531ECS00003213010.009三 查询知识注:以下查询基于ecshop网站的商品表(ecs_goods)在练习时可以只取部分列,方便查看.1: 基础查询 where的练习:查出满足以下条件的商品1.1:主键为32的商品select goods_id,goods_name,shop_price from ecs_goods where goods_id=32;1.2:不属第3栏目的所有商品select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3; 1.3:本店价格高于3000元的商品select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price >3000; 1.4:本店价格低于或等于100元的商品select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;1.5:取出第4栏目或第11栏目的商品(不许用or)select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id in (4,11); 1.6:取出100<=价格<=500的商品(不许用and)select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price between 100 and 500; 1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或not in分别实现)select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 and shop_price 4000 and shop_price <5000;1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods wherecat_id=3 and (shop_price 3000) and click_count>5;1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)select goods_id,cat_id,goods_name,shop_price,click_count from ecs_goods where cat_id in (2,3,4,5); 1.11:取出名字以"诺基亚"开头的商品select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚%';1.12:取出名字为"诺基亚Nxx"的手机select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '诺基亚N__';1.13:取出名字不以"诺基亚"开头的商品select goods_id,cat_id,goods_name,shop_price from ecs_goos where goods_name not like '诺基亚%'; 1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5 "诺基亚"开头的系列商品select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id=3 and shop_price>1000 and shop_price 5 and goods_name like '诺基亚%';select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like '诺基亚%';1.15 一道面试题有如下表和数据,查出num>=20 and num<=39的数字,并且,把num值处于[20,29]之间,显示为20num值处于[30,39]之间的,显示30mian表num312152523293437324548521.16 练习题:把good表中商品名为'诺基亚xxxx'的商品,改为'HTCxxxx',提示:大胆的把列看成变量,参与运算,甚至调用函数来处理 .substring(),concat()2 分组查询group:2.1:查出最贵的商品的价格select max(shop_price) from ecs_goods;2.2:查出最大(最新)的商品编号select max(goods_id) from ecs_goods;2.3:查出最便宜的商品的价格select min(shop_price) from ecs_goods;2.4:查出最旧(最小)的商品编号select min(goods_id) from ecs_goods;2.5:查询该店所有商品的库存总量select sum(goods_number) from ecs_goods;2.6:查询所有商品的平均价 select avg(shop_price) from ecs_goods;2.7:查询该店一共有多少种商品 select count(*) from ecs_goods;2.8:查询每个栏目下面最贵商品价格最低商品价格商品平均价格商品库存量商品种类提示:(5个聚合函数,sum,avg,max,min,count与group综合运用)select cat_id,max(shop_price) from ecs_goods group by cat_id;3 having与group综合运用查询:3.1:查询该店的商品比市场价所节省的价格select goods_id,goods_name,market_price-shop_price as j from ecs_goods ; 3.2:查询每个商品所积压的货款(提示:库存*单价)select goods_id,goods_name,goods_number*shop_price from ecs_goods3.3:查询该店积压的总货款select sum(goods_number*shop_price) from ecs_goods;3.4:查询该店每个栏目下面积压的货款.select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_id;3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)select goods_id,goods_name,market_price-shop_price as k from ecs_goodswhere market_price-shop_price >200;select goods_id,goods_name,market_price-shop_price as k from ecs_goodshaving k >200;3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款select cat_id,sum(goods_number*shop_price) as k from ecs_goods group by cat_idhaving k>200003.7:where-having-group综合练习题有如下表及数据namesubjectscore张三数学90张三语文50张三地理40李四语文55李四政治45王五政治30要求:查询出2门及2门以上不及格者的平均成绩一种错误做法mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;namekavg(score)张三360.0000李四250.00002 rows in set (0.00 sec)mysql> select name,count(score<60) as k,avg(score) from stu group by name;namekavg(score)张三360.0000李四250.0000王五130.00003 rows in set (0.00 sec)mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;namekavg(score)张三360.0000李四250.00002 rows in set (0.00 sec)加上赵六后错误暴露mysql> insert into stu-> values -> ('赵六','A',100), -> ('赵六','B',99), -> ('赵六','C',98);Query OK, 3 rows affected (0.05 sec)Records: 3 Duplicates: 0 Warnings: 0错误显现mysql> select name,count(score<60) as k,avg(score) from stu group by name having k>=2;namekavg(score)张三360.0000李四250.0000赵六399.00003 rows in set (0.00 sec)正确思路,先查看每个人的平均成绩mysql> select name,avg(score) from stu group by name;nameavg(score)张三60.0000李四50.0000王五30.0000赵六99.00004 rows in set (0.00 sec)mysql> # 看每个人挂科情况mysql> select name,score < 60 from stu;namescore < 60张三0张三1张三1李四1李四1王五1赵六0赵六0赵六09 rows in set (0.00 sec)mysql> #计算每个人的挂科科目mysql> select name,sum(score < 60) from stu group by name;namesum(score < 60)张三2李四2王五1赵六04 rows in set (0.00 sec)同时计算每人的平均分mysql> select name,sum(score < 60),avg(score) as pj from stu group by name;namesum(score < 60)pj张三260.0000李四250.0000王五130.0000赵六099.00004 rows in set (0.00 sec)利用having筛选挂科2门以上的.mysql> select name,sum(score < 60) as gk ,avg(score) as pj from stu group by name having gk >=2;namegkpj张三260.0000李四250.00002 rows in set (0.00 sec)4: order by 与 limit查询4.1:按价格由高到低排序select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc;4.2:按发布时间由早到晚排序select goods_id,goods_name,add_time from ecs_goods order by add_time;4.3:接栏目由低到高排序,栏目内部按价格由高到低排序select goods_id,cat_id,goods_name,shop_price from ecs_goods order by cat_id ,shop_price desc; 4.4:取出价格最高的前三名商品select goods_id,goods_name,shop_price from ecs_goods order by shop_price desc limit 3;4.5:取出点击量前三名到前5名的商品select goods_id,goods_name,click_count from ecs_goods order by click_count desc limit 2,3;5 连接查询5.1:取出所有商品的商品名,栏目名,价格select goods_name,cat_name,shop_price from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_id;5.2:取出第4个栏目下的商品的商品名,栏目名,价格select goods_name,cat_name,shop_price from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_idwhere ecs_goods.cat_id = 4;5.3:取出第4个栏目下的商品的商品名,栏目名,与品牌名select goods_name,cat_name,brand_name from ecs_goods left join ecs_categoryon ecs_goods.cat_id=ecs_category.cat_idleft join ecs_brand on ecs_goods.brand_id=ecs_brand.brand_idwhere ecs_goods.cat_id = 4;5.4: 用友面试题根据给出的表结构按要求写出SQL语句。Match 赛程表字段名称 字段类型 描述matchID int 主键hostTeamID int 主队的IDguestTeamID int 客队的IDmatchResult varchar(20) 比赛结果，如（2:0）matchTime date 比赛开始时间Team 参赛队伍表字段名称 字段类型 描述teamID int 主键teamName varchar(20) 队伍名称Match的hostTeamID与guestTeamID都与Team中的teamID关联查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出：拜仁 2：0 不来梅 2006-6-21mysql> select * from m;midhidgidmresmatime1122:02006-05-212231:22006-06-213312:52006-06-254213:22006-07-214 rows in set (0.00 sec)mysql> select * from t;tidtname1国安2申花3公益联队3 rows in set (0.00 sec)mysql> select hid,t1.tname as hname ,mres,gid,t2.tname as gname,matime-> from -> m left join t as t1 -> on m.hid = t1.tid -> left join t as t2 -> on m.gid = t2.tid;hidhnamemresgidgnamematime1国安2:02申花2006-05-212申花1:23公益联队2006-06-213公益联队2:51国安2006-06-252申花3:21国安2006-07-214 rows in set (0.00 sec)6 union查询6.1:把ecs_comment,ecs_feedback两个表中的数据,各取出4列,并把结果集union成一个结果集.6.2:3期学员碰到的一道面试题A表:idnuma5b10c15d10B表:idnumb5c15d20e99要求查询出以下效果:idnuma5b15c30d30e99create table a (id char(1),num int)engine myisam charset utf8;insert into a values ('a',5),('b',10),('c',15),('d',10);create table b (id char(1),num int)engine myisam charset utf8;insert into b values ('b',5),('c',15),('d',20),('e',99);mysql> # 合并 ,注意all的作用mysql> select * from ta-> union all -> select * from tb;idnuma5b10c15d10b5c15d20e99参考答案:mysql> # sum,group求和mysql> select id,sum(num) from (select from ta union all select from tb) as tmp group by id;idsum(num)a5b15c30d30e995 rows in set (0.00 sec)7: 子查询:7.1:查询出最新一行商品(以商品编号最大为最新,用子查询实现)select goods_id,goods_name from ecs_goods where goods_id =(select max(goods_id) from ecs_goods); 7.2:查询出编号为19的商品的栏目名称(用左连接查询和子查询分别)7.3:用where型子查询把ecs_goods表中的每个栏目下面最新的商品取出来select goods_id,goods_name,cat_id from ecs_goods where goods_id in (select max(goods_id) from ecs_goods group by cat_id);7.4:用from型子查询把ecs_goods表中的每个栏目下面最新的商品取出来select * from (select goods_id,cat_id,goods_name from ecs_goods order by goods_id desc) as t group by cat_id;7.5 用exists型子查询,查出所有有商品的栏目select * from categorywhere exists (select * from goods where goods.cat_id=category.cat_id);创建触发器:CREATE trigger tg2after insert on ordfor each rowupdate goods set goods_number=goods_number-new.num where id=new.gidCREATE trigger tg3after delete on ordfor each rowupdate goods set goods_number=good_number+old.num where id=old.gidCREATE trigger tg4after update on ordfor each rowupdate goods set goods_number=goods_number+old.num-new.num where id=old.gid2012-03-25更新,添加了面试案例

安装MySQL 8保姆级教程 MySQL 8.0安装部署MySQL 8 正式版 8.0.11 已发布，官方表示 MySQL 8 要比 MySQL 5.7 快 2 倍，还带来了大量的改进和更快的性能！一. Mysql8.0版本相比之前版本的一些特性1) 默认编码utf8默认编码使用utf8mb4, utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符历史，MySQL数据库的 "utf8"并不是真正概念里的 UTF-8。MySQL中的"utf8"编码只支持最大3字节每字符。真正的大家正在使用的UTF-8编码是应该能支持4字节每个字符。MySQL的开发者没有修复这个bug。他们在2010年增加了一个变通的方法：一个新的字符集"utf8mb4"另外，utf-32编码固定使用4字节，32bit存储相比utf8浪费空间。2) 降序索引在之前的版本中可以创建，但是实际创建的还是升序索引mysql> create table t1(id1 int,id2 int,key(id1,id2 desc)); Query OK, 0 rows affected (0.10 sec) mysql> show create table t1 \G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id1` int(11) DEFAULT NULL, `id2` int(11) DEFAULT NULL, KEY `id1` (`id1`,`id2` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ERROR: No query specified另外，由于降序索引的引入，MySQL 8.0再也不会对group by操作进行隐式排序3) 隐藏索引隐藏索引的特性对于性能调试非常有用。当一个索引隐藏时，它不会被查询优化器所使用。也就是说，我们可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了.mysql> create index idx2 on t1(id1); Query OK, 0 rows affected (0.35 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 alter index idx2 invisible; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t1 where key_name='idx2' \G; *************************** 1. row *************************** Table: t1 Non_unique: 1 Key_name: idx2 Seq_in_index: 1 Column_name: id1 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: NO 1 row in set (0.01 sec) ERROR: No query specified当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，此特性是专门为优化调试使用。如果长期隐藏一个索引，那还不如干脆删掉，因为毕竟索引存在会影响插入、更新和删除的性能.4) 设置持久化在oracle中可以使用scope设置参数确定是否到spfile, 现在mysql8.0也可以放在文件中了，使用方法:mysql> show variables like 'max_connects'; Empty set (0.07 sec) mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 500 | +-----------------+-------+ 1 row in set (0.01 sec) mysql> set persist max_connections=1000; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1000 | +-----------------+-------+ 1 row in set (0.01 sec)可以查看在数据目录datadir中的一个mysqld-auto.cnf文件中保存了设置，下次启动时候将用此配置覆盖默认配置参数{ "Version" : 1 , "mysql_server" : { "max_connections" : { "Value" : "1000" , "Metadata" : { "Timestamp" : 1540437420567571 , "User" : "root" , "Host" : "" } } } }5) 通用表表达式（Common Table Expressions）也可以称为虚拟视图，大大简化复杂查询mysql> WITH -> t1 AS (SELECT * FROM t1), -> t2 AS (SELECT * FROM t1) -> SELECT t1.*, t2.* -> FROM t1, t2; +------+------+------+------+ | id1 | id2 | id1 | id2 | +------+------+------+------+ | 1 | 2 | 1 | 2 | +------+------+------+------+ 1 row in set (0.00 sec) 6) 窗口函数（Window Functions）MySQL 被吐槽最多的特性之一就是缺少 rank() 函数，当需要在查询当中实现排名时，必须手写 @ 变量。但是从 8.0 开始，MySQL 新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式说明，窗口可以单独创建。mysql> create table tbl2(name varchar(10),amount int); Query OK, 0 rows affected (0.16 sec) mysql> insert into tbl2 values('usa',100),('china',101),('japan',103),('russian',99); Query OK, 4 rows affected (0.13 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select t1.*,rank() over w as 'rank' from tbl2 as t1 window w as (order by amount); +---------+--------+------+ | name | amount | rank | +---------+--------+------+ | russian | 99 | 1 | | usa | 100 | 2 | | china | 101 | 3 | | japan | 103 | 4 | +---------+--------+------+ 4 rows in set (0.00 sec) mysql> select t1.*,rank() over() as 'rank' from tbl2 as t1; +---------+--------+------+ | name | amount | rank | +---------+--------+------+ | usa | 100 | 1 | | china | 101 | 1 | | japan | 103 | 1 | | russian | 99 | 1 | +---------+--------+------+ 4 rows in set (0.00 sec) mysql> select t1.*,rank() over(order by amount) as 'rank' from tbl2 as t1; +---------+--------+------+ | name | amount | rank | +---------+--------+------+ | russian | 99 | 1 | | usa | 100 | 2 | | china | 101 | 3 | | japan | 103 | 4 | +---------+--------+------+ 4 rows in set (0.00 sec) mysql> select t1.*,sum() over() as 'rank' from tbl2 as t1; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') over() as 'rank' from tbl2 as t1' at line 1 mysql> select t1.*,sum(amount) over() as 'sum' from tbl2 as t1; +---------+--------+------+ | name | amount | sum | +---------+--------+------+ | usa | 100 | 403 | | china | 101 | 403 | | japan | 103 | 403 | | russian | 99 | 403 | +---------+--------+------+ 4 rows in set (0.00 sec)7) 安全性对 OpenSSL 的改进、新的默认身份验证、SQL 角色、密码强度、授权。MySQL现在维护关于密码历史的信息，允许对以前密码的重用进行限制比如常见的一个连接错误，参考：https://blog.csdn.net/jc_benben/article/details/806528978) 增加JSON （json enhancements）AND OpenGIS spatial typesmysql提供了很多json相关的函数和API接口，添加了基于路径查询参数从 JSON 字段中抽取数据的 JSON_EXTRACT() 函数，以及用于将数据分别组合到 JSON 数组和对象中的 JSON_ARRAYAGG() 和 JSON_OBJECTAGG() 聚合函数9) 原子数据定义语句（原子DDL）（Atomic Data Definition Statements (Atomic DDL)）10) 资源管理（Resource management）MySQL现在支持资源组的创建和管理，并允许将服务器内运行的线程分配给特定组，以便线程根据组可用的资源执行。组属性可以控制其资源，以启用或限制组中线程的资源消耗11) Data dictionaryMySQL现在包含一个事务数据字典，用于存储有关数据库对象的信息，In previous MySQL releases, dictionary data was stored in metadata files and nontransactional tables比如myisam和innodb表定义frm文件等.12) innodb的增强每次值更改时，当前最大自动增量计数器值将写入重做日志，并保存到每个检查点上的引擎专用系统表中。这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。遇到索引树损坏时， InnoDB将损坏标志写入重做日志，这会使损坏标志崩溃安全。InnoDB还将内存中损坏标志数据写入每个检查点上的引擎专用系统表。在恢复期间， InnoDB从两个位置读取损坏标志并在将内存表和索引对象标记为损坏之前合并结果。InnoDB 分布式缓存插件支持多个get操作（读取在一个单一的多键/值对分布式缓存查询）和范围查询;新的动态配置选项 innodb_deadlock_detect可用于禁用死锁检测。在高并发系统上，当许多线程等待同一个锁时，死锁检测会导致速度减慢。有时，禁用死锁检测可能更有效，并且在innodb_lock_wait_timeout发生死锁时依赖于事务回滚的设置。INFORMATION_SCHEMA.INNODB_CACHED_INDEXES 表报告InnoDB每个索引缓冲池中缓存的索引页数 。InnoDB现在，在共享临时表空间中创建临时表ibtmp1。InnoDB 表空间加密功能支持重做日志的加密和撤消日志数据;InnoDB支持 NOWAIT和SKIP LOCKED选项SELECT ... FOR SHARE以及SELECT ... FOR UPDATE锁定读取语句。 NOWAIT如果请求的行被另一个事务锁定，则会立即返回该语句。SKIP LOCKED从结果集中删除锁定的行SELECT ... FOR SHARE替换 SELECT ... LOCK IN SHARE MODE，但LOCK IN SHARE MODE仍可用于向后兼容;支持ADD PARTITION，DROP PARTITION，COALESCE PARTITION，REORGANIZE PARTITION，和REBUILD PARTITION ALTER TABLE选项;InnoDB存储引擎现在使用MySQL的数据字典，而不是它自己的存储引擎特定的数据字典;mysql系统表和数据字典表现在在MySQL数据目录中InnoDB命名的单个表空间文件创建mysql. ibd。以前，这些表是InnoDB在mysql数据库目录中的各个表空间文件中创建的;二. Mysql8.0安装 (YUM方式)1) 首先删除系统默认或之前可能安装的其他版本的mysql[root@DB-node01 ~]# for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done [root@DB-node01 ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf2) 安装Mysql8.0 的yum资源库mysql80-community-release-el7-1.noarch.rpm 下载地址: https://pan.baidu.com/s/1QzYaSnzAQeTqAmk8FE9doA提取密码: 2maw[root@DB-node01 ~]# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm3) 安装Mysql8.0[root@DB-node01 ~]# yum install mysql-community-server #启动MySQL服务器和MySQL的自动启动 [root@DB-node01 ~]# systemctl start mysqld [root@DB-node01 ~]# systemctl enable mysqld4) 使用默认密码初次登录后, 必须要重置密码查看默认密码, 如下默认密码为"e53xDalx.*dE"[root@DB-node01 ~]# grep 'temporary password' /var/log/mysqld.log 2019-03-06T01:53:19.897262Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: e53xDalx.*dE [root@DB-node01 ~]# mysql -pe53xDalx.*dE ............ mysql> select version(); ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.报错提示必须要重置初始密码, 下面开始重置mysql登录密码（注意要切换到mysql数据库，使用use mysql）mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements这个其实与validate_password_policy的值有关, mysql8.0更改了validate_password_policy相关的配置名称, 这跟Mysql5.7有点不一样了.mysql> set global validate_password.policy=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password.length=1; Query OK, 0 rows affected (0.00 sec)接着再修改密码mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.05 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)退出, 重新使用新密码登录mysql[root@DB-node01 ~]# mysql -p123456 ........... mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.15 | +-----------+ 1 row in set (0.00 sec)查看服务端口mysql> show global variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0.01 sec)查看mysql连接的授权信息mysql> select host,user,password from mysql.user; ERROR 1054 (42S22): Unknown column 'password' in 'field list'上面这是mysql5.6及以下版本的查看命令,mysql5.7之后的数据库里mysql.user表里已经没有password这个字段了，password字段改成了authentication_string。mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005${7J0=4Dc7Jym8eI/FU4jimKWFvkD9XmoAkF1ca5.Un0bc6zgmPtU.0 | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec)=============================mysql8.0修改用户密码命令================================mysql> use mysql; mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; mysql> flush privileges;三. Mysql8.0安装 (二进制方式)1) 首先删除系统默认或之前可能安装的其他版本的mysql[root@mysql8-node ~]# for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done [root@mysql8-node ~]# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf2) 安装需要的软件包[root@mysql8-node ~]# yum -y install libaio [root@mysql8-node ~]# yum -y install net-tools3) 下载并安装Mysql8.0.12下载地址: https://pan.baidu.com/s/1LyXrkrCPP7QKLrWYbLRBlw提取密码: emmf[root@mysql8-node ~]# groupadd mysql [root@mysql8-node ~]# useradd -g mysql mysql [root@mysql8-node ~]# cd /usr/local/src/ [root@mysql-node src]# ll -rw-r--r-- 1 root root 620389228 Aug 22 2018 mysql8.0.12_bin_centos7.tar.gz [root@mysql-node src]# tar -zvxf mysql8.0.12_bin_centos7.tar.gz [root@mysql-node src]# mv mysql /usr/local/ [root@mysql-node src]# chown -R mysql.mysql /usr/local/mysql [root@mysql-node src]# vim /home/mysql/.bash_profile export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH [root@mysql-node src]# source /home/mysql/.bash_profile [root@mysql-node src]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@mysql-node src]# source /etc/profile4) 创建数据目录[root@mysql-node src]# mkdir -p /data/mysql/{data,log,binlog,conf,tmp} [root@mysql-node src]# chown -R mysql.mysql /data/mysql5) 配置mysql[root@mysql-node src]# su - mysql [mysql@mysql-node ~]$ vim /data/mysql/conf/my.cnf [mysqld] lower_case_table_names = 1 user = mysql server_id = 1 port = 3306 default-time-zone = '+08:00' enforce_gtid_consistency = ON gtid_mode = ON binlog_checksum = none default_authentication_plugin = mysql_native_password datadir = /data/mysql/data pid-file = /data/mysql/tmp/mysqld.pid socket = /data/mysql/tmp/mysqld.sock tmpdir = /data/mysql/tmp/ skip-name-resolve = ON open_files_limit = 65535 table_open_cache = 2000 #################innodb######################## innodb_data_home_dir = /data/mysql/data innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend innodb_buffer_pool_size = 12000M innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 600 innodb_lock_wait_timeout = 120 innodb_log_buffer_size = 8M innodb_log_file_size = 200M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 85 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 32 innodb_file_per_table innodb_rollback_on_timeout innodb_undo_directory = /data/mysql/data innodb_log_group_home_dir = /data/mysql/data ###################session########################### join_buffer_size = 8M key_buffer_size = 256M bulk_insert_buffer_size = 8M max_heap_table_size = 96M tmp_table_size = 96M read_buffer_size = 8M sort_buffer_size = 2M max_allowed_packet = 64M read_rnd_buffer_size = 32M ############log set################### log-error = /data/mysql/log/mysqld.err log-bin = /data/mysql/binlog/binlog log_bin_index = /data/mysql/binlog/binlog.index max_binlog_size = 500M slow_query_log_file = /data/mysql/log/slow.log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 10 log_slow_admin_statements = ON log_output = FILE,TABLE master_info_file = /data/mysql/binlog/master.6) 初始化 (稍等一会儿, 可以到/data/mysql/log/mysqld.err日子里查看初始化过程, 看看有没有error信息)[mysql@mysql-node ~]$ mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql 7) 启动mysqld[mysql@mysql-node ~]$ mysqld_safe --defaults-file=/data/mysql/conf/my.cnf & [mysql@mysql-node ~]$ lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql (LISTEN)8) 登录mysql, 重置密码本地首次使用sock文件登录mysql是不需要密码的[mysql@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock ............. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.07 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec)退出, 此时密码重置后, 就不能使用sock文件无密码登录了[root@mysql-node ~]# mysql -S /data/mysql/tmp/mysqld.sock ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@mysql-node ~]# mysql -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)做sock文件的软链接[root@mysql-node ~]# ln -s /data/mysql/tmp/mysqld.sock /tmp/mysql.sock [root@mysql-node ~]# mysql -p123456 或者 [root@mysql-node ~]# mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456 ............. mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec) #授予用户权限. 必须先要创建用户, 才能授权!! (创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!) mysql> create user 'kevin'@'%' identified by '123456'; Query OK, 0 rows affected (0.11 sec) mysql> grant all privileges on *.* to 'kevin'@'%' with grant option; Query OK, 0 rows affected (0.21 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> update mysql.user set host='172.16.60.%' where user="kevin"; Query OK, 1 row affected (0.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.05 sec) mysql> select host,user,authentication_string from mysql.user; +-------------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-------------+------------------+------------------------------------------------------------------------+ | 172.16.60.% | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> create user 'bobo'@'172.16.60.%' identified by '123456'; Query OK, 0 rows affected (0.09 sec) mysql> grant all privileges on *.* to 'bobo'@'172.16.60.%'; Query OK, 0 rows affected (0.17 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec) mysql> select host,user,authentication_string from mysql.user; +-------------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-------------+------------------+------------------------------------------------------------------------+ | 172.16.60.% | bobo | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 172.16.60.% | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec) mysql> show grants for kevin@'172.16.60.%'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for kevin@172.16.60.% | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `kevin`@`172.16.60.%` WITH GRANT OPTION | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)MySQL单机多实例安装配置通过上面二进制部署可知, 已经起来一个3306端口的MySQL实例, 现在需要再起来两个实例, 分别为3307, 3308. 操作如下:创建实例的数据目录[root@mysql-node ~]# mkdir -p /data/mysql3307/{data,log,binlog,conf,tmp} [root@mysql-node ~]# mkdir -p /data/mysql3308/{data,log,binlog,conf,tmp} [root@mysql-node ~]# chown -R mysql.mysql /data/mysql3307 [root@mysql-node ~]# chown -R mysql.mysql /data/mysql3308配置mysql[root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3307/conf/ [root@mysql-node ~]# cp -r /data/mysql/conf/my.cnf /data/mysql3308/conf/ [root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3307/#g' /data/mysql3307/conf/my.cnf [root@mysql-node ~]# sed -i 's#/data/mysql/#/data/mysql3308/#g' /data/mysql3308/conf/my.cnf [root@mysql-node ~]# sed -i 's/3306/3307/g' /data/mysql3307/conf/my.cnf [root@mysql-node ~]# sed -i 's/3306/3308/g' /data/mysql3308/conf/my.cnf [root@mysql-node ~]# chown -R mysql.mysql /data/mysql*进行初始化两个实例[root@mysql-node ~]# mysqld --defaults-file=/data/mysql3307/conf/my.cnf --initialize-insecure --user=mysql [root@mysql-node ~]# mysqld --defaults-file=/data/mysql3308/conf/my.cnf --initialize-insecure --user=mysql接着启动mysqld[root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf & [root@mysql-node ~]# mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf & [root@mysql-node ~]# ps -ef|grep mysql mysql 23996 1 0 14:37 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf mysql 24743 23996 0 14:38 ? 00:00:17 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/mysql/log/mysqld.err --open-files-limit=65535 --pid-file=/data/mysql/tmp/mysqld.pid --socket=/data/mysql/tmp/mysqld.sock --port=3306 root 30473 23727 0 15:33 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf mysql 31191 30473 17 15:33 pts/0 00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3307/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3307/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3307/log/mysqld.err --open-files-limit=65535 --pid-file=/data/mysql3307/tmp/mysqld.pid --socket=/data/mysql3307/tmp/mysqld.sock --port=3307 root 31254 23727 0 15:33 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf mysql 31977 31254 39 15:33 pts/0 00:00:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3308/conf/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql3308/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql3308/log/mysqld.err --open-files-limit=65535 --pid-file=/data/mysql3308/tmp/mysqld.pid --socket=/data/mysql3308/tmp/mysqld.sock --port=3308 root 32044 23727 0 15:34 pts/0 00:00:00 grep --color=auto mysql [root@mysql-node ~]# lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 31191 mysql 22u IPv6 23144844 0t0 TCP *:opsession-prxy (LISTEN) [root@mysql-node ~]# lsof -i:3308 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 31977 mysql 22u IPv6 23145727 0t0 TCP *:tns-server (LISTEN) [root@mysql-node ~]# lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql (LISTEN)登录3307端口实例, 并设置密码[root@mysql-node ~]# mysql -S /data/mysql3307/tmp/mysqld.sock ............ mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.11 sec) mysql> flush privileges; Query OK, 0 rows affected (0.11 sec)退出, 使用新密码登录[root@mysql-node ~]# mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456 ............. mysql>同理, 登录3308端口实例, 并设置密码[root@mysql-node ~]# mysql -S /data/mysql3308/tmp/mysqld.sock ........... mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.13 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)退出, 使用新密码登录[root@mysql-node ~]# mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456 .................... mysql>==========================================3306, 3307, 3308三个端口实例的启动命令分别为:mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &登录命令分别为:mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456编写mysql多端口实例的登录脚本#上面的多端口实例配置之后, 查看一个登录脚本:myin (注意将脚本中的密码改为自己的密码) [root@mysql-node ~]# ll /usr/local/mysql/bin/myin -rwxrwxr-x 1 mysql mysql 161 Aug 22 2018 /usr/local/mysql/bin/myin [root@mysql-node ~]# cat /usr/local/mysql/bin/myin #!/bin/bash p=$1 shift mysql -h"127.0.0.1" -P"$p" --default-character-set=utf8mb4 --show-warnings -uroot -p'123456' -A --prompt="(\u@\p)[\d]> " "$@" #尝试登录 [mysql@mysql-node ~]# myin 3306 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server #这是因为各个端口实例中的root用户授权的是localhost, 而不是127.0.0.1, 则可以再次添加一个针对127.0.0.1的root用户授权 #或者将myin脚本中的127.0.0.1 修改为 localhost [root@mysql-node ~]# sed -i 's/127.0.0.1/localhost/g' /usr/local/mysql/bin/myin [root@mysql-node ~]# cat /usr/local/mysql/bin/myin #!/bin/bash p=$1 shift mysql -h"localhost" -P"$p" --default-character-set=utf8mb4 --show-warnings -uroot -p'123456' -A --prompt="(\u@\p)[\d]> " "$@"则现在可以登录各个实例了, 登录命令为"myin port"登录3306端口实例[root@mysql-node ~]# myin 3306 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@mysql.sock)[(none)]>登录3307端口实例[root@mysql-node ~]# myin 3307 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 28 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@mysql.sock)[(none)]>登录3308端口实例[root@mysql-node ~]# myin 3308 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 Server version: 8.0.12 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@mysql.sock)[(none)]>生产环境下的思路:同一个业务库单独放在一个实例下, 不要混用数据库. 单机多实例间可以做主从或主主同步, 也可以在多机多实例之间坐主从或主主同步.比如:A机器有三个实例: 3310, 3311, 3312B机器有三个实例: 3310, 3311, 3312C机器有三个实例: 3310, 3311, 3312 则三台机器的每个对应的端口实例之间做主从或主主同步(GTID, MGR), 一主两从, 或三主模式. (多实例环境最好不要用3306端口, 否则经常会出现一些误操作)四. Mysql8.0使用过程中踩过的一些坑1) 创建用户和授权在mysql8.0创建用户和授权和之前不太一样了，其实严格上来讲，也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。mysql> create user 'kevin'@'%' identified by '123456'; Query OK, 0 rows affected (0.04 sec) mysql> grant all privileges on *.* to 'kevin'@'%' with grant option; Query OK, 0 rows affected (0.04 sec) mysql> create user 'bobo'@'%' identified by '123456'; Query OK, 0 rows affected (0.06 sec) mysql> grant all privileges on *.* to 'bobo'@'%' with grant option; Query OK, 0 rows affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 | +-----------+------------------+------------------------------------------------------------------------+如果还是用Mysql5.7及之前版本的直接授权的方法, 会有报错:mysql> grant all privileges on *.* to 'shibo'@'%' identified by '123456'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456'' at line 12) Mysql8.0默认是不能使用root账号进行远程登录的! root账号只能本地登录!mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 | +-----------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec)如果想要远程登录, 则需要进行update更新下root账号的权限mysql> update mysql.user set host='%' where user="root"; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.14 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB#R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | % | root | $A$005$/VO_y^7,]6;2qxggBLmJzhA0Qylu5/AHuRScZ/ykKedgZKh/6krOIzPs2 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | +-----------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec)这样就能在远程使用root账号登录该mysql8.0的数据库了3) 修改root账号权限, 允许root账号远程登录后, 用navicat进行mysql的远程连接时，出现了弹窗报错：出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种:1) 一种是升级navicat驱动;2) 一种是把mysql用户登录密码加密规则还原成mysql_native_password;这里选择第二种方法来解决:#修改加密规则 mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.16 sec) #更新一下用户的密码 mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; Query OK, 0 rows affected (0.08 sec) #刷新权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.03 sec)这样问题就解决了。4) sqlyog链接时出现2058异常1) 使用sqlyog链接时会出现2058的异常，此时我们需要修改mysql，命令行登录mysql（与修改密码中登录相同，使用修改后的密码），然后执行下面的命令： mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';其中password为自己修改的密码。然后SQLyog中重新连接，则可连接成功，OK。2) 如果报错：ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'则使用下面命令： mysql > ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';5) 修改默认编码方式mysql8.0默认编码方式为utf8mb4，因此使用时不需要修改，可使用如下命令查看：mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';如果需要修改其他编码方式，比如需要修改为utf8mb4，可以使用如下方式：修改mysql配置文件my.cnf, 找到后请在以下三部分里添加如下内容：[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4'然后重启mysqld服务即可, 其中:character_set_client (客户端来源数据使用的字符集)character_set_connection (连接层字符集)character_set_database (当前选中数据库的默认字符集)character_set_results (查询结果字符集)character_set_server (默认的内部操作字符集)========================================数据库连接参数中:characterEncoding=utf8 会被自动识别为utf8mb4，也可以不加这个参数，会自动检测。而autoReconnect=true 是必须加上的。6) 部分参数配置查询命令#查询mysql最大连接数设置 mysql> show global variables like 'max_conn%'; mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections'; # 查看最大链接数 mysql> show global status like 'Max_used_connections'; # 查看慢查询日志是否开启以及日志位置 mysql> show variables like 'slow_query%'; # 查看慢查询日志超时记录时间 mysql> show variables like 'long_query_time'; # 查看链接创建以及现在正在链接数 mysql> show status like 'Threads%'; # 查看数据库当前链接 mysql> show processlist; # 查看数据库配置 mysql> show variables like '%quer%';

如何通过索引优化PHP与MySQL的多表查询？ 如何通过索引优化PHP与MySQL的多表查询？索引是数据库中用于提高查询性能的一种数据结构，它可以加快数据的查找速度。在PHP与MySQL的多表查询中，合理的索引设计可以显著提升查询速度，下面将介绍一些常用的索引优化方法。单表索引优化在进行多表查询之前，首先需要确保每个表都有恰当的索引。对于经常用于查询条件的列，可以为其添加索引。例如，对于一个用户表，经常使用user_id进行查询，可以为user_id列添加索引。示例代码：ALTER TABLE user ADD INDEX idx_user_id (user_id);外键索引优化对于多表查询，经常使用外键来关联多个表。外键是用于保持数据完整性的一种约束，但是它不会自动生成索引。因此，在使用外键进行多表查询时，可以为外键列添加索引。示例代码：ALTER TABLE order ADD INDEX idx_user_id (user_id); ALTER TABLE order ADD FOREIGN KEY (user_id) REFERENCES user (user_id);联合索引优化当多个列同时参与查询时，可以考虑使用联合索引。联合索引是指为多个列创建一个索引，这样就可以通过一个索引同时查找多个列。通常，我们需要根据查询的频率和查询的列组合来决定是否创建联合索引。示例代码：ALTER TABLE product ADD INDEX idx_category_brand (category_id, brand_id);覆盖索引优化当多表查询的结果只需要一部分列时，可以使用覆盖索引来提高性能。覆盖索引是指通过索引即可获取查询所需的全部数据，而不必再回表查询数据。通常，我们需要根据查询的需求来选择合适的索引列。示例代码：SELECT user_id, user_name FROM user WHERE user_id = 1;查询优化器使用提示MySQL的查询优化器负责解析和执行查询语句，它会根据统计信息和算法来选择执行计划。然而，有时候优化器的选择可能并不理想。在某些情况下，可以通过使用查询优化器的提示来引导优化器选择更合适的执行计划。示例代码：SELECT /+ index(orders) / * FROM orders WHERE user_id = 1;通过合理的索引优化方法，可以显著提高PHP与MySQL的多表查询性能。在实际开发中，可以根据具体情况选择适用的索引优化方法，并结合数据库的实际情况进行调整，以获得更好的查询效率。