Mysql使用
mysql 8.0.20 安装配置详细教程
mysql 8.0.20 安装配置详细教程_Mysql_脚本之家
1. 下载解压安装包
2. 配置文件my.ini，建立 Data目录，都和bin同级
[mysqld]
#设置3306端口
port=3306
#设置mysql的安装目录
basedir=D:\env\mysql-8.0.26-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\env\mysql-8.0.26-winx64\Data
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
3. 配置环境变量
4. mysqld install // 安装服务 卸载为 mysqld -remove mysql
5. mysqld --initialize --console 初始化， 初始化完成后会有初始密码打印在控制台，注意查看 root@localhost 后的密码
6. net start mysql // 如果这里提示找不到mysql服务，就看看是不是4步骤没做
7. mysql -u root -p // 接下来会输入密码，就是步骤5中给的
8. ALTER USER root@localhost IDENTIFIED BY ‘123456'; // 修改默认密码
解决 “Too many connections”问题
show variables like '%max_connections%' # 查看最大连接数
show full processlist; # 查看所有链接
#1. **清理mysql的sleep链接
# shell
#It is used to kill processlist of mysql sleep
#!/bin/sh
MYSQLDIR=/usr/bin
MYSQLTOOL=$MYSQLDIR/mysql
MYSQLUSER=server
MYSQLPASS="Server@123456"
usage()
{
echo "Usage: $0 ip port user db"
echo "Example: $0 1.1.1.1 3306 server dx-shop"
exit
}
if [ $# -ne 4 ];then
usage
fi
hostip=$1
port=$2
user=$3
db=$4
sleepid="SELECT ID FROM information_schema.PROCESSLIST t WHERE t.COMMAND='Sleep' and user='$3' and DB='$4' and time >1000;"
echo $sleepid
$MYSQLTOOL -h$hostip -u$MYSQLUSER -p$MYSQLPASS -P$port -e "$sleepid" >sleepIDs
while read sID
do
echo $sID
$MYSQLTOOL -h$hostip -u$MYSQLUSER -p$MYSQLPASS -P$port -e "kill $sID"
done<sleepIDs
# 查看事务占用
select * from information_schema.innodb_trx
# 2. 修改最大连接数
# 2.1
set global max_connections=14000; # 临时修改，数据库重启就会变为原来的
# 2.2 在my.conf 参数中修改，不过需要重新启动mysql 服务！不推荐**
删除多个字段唯一的重复行
# 记得备份!!!
# 记得备份!!!
# 记得备份!!!
DELETE
FROM
vivo_theme_detail_info_pipeline
WHERE
(package_id,
theme_type,
phone_model,
oneday) IN (
SELECT
t.package_id,
t.theme_type,
t.phone_model,
t.oneday
FROM
(
SELECT
package_id,
theme_type,
phone_model,
oneday
FROM
vivo_theme_detail_info_pipeline
GROUP BY
package_id,
theme_type,
phone_model,
oneday
HAVING
count(1) > 1
) t
)
AND id NOT IN (
SELECT
dt.minid
FROM
(
SELECT
min(id) AS minid
FROM
vivo_theme_detail_info_pipeline
GROUP BY
package_id,
theme_type,
phone_model,
oneday
HAVING
count(1) > 1
) dt
)
DELETE
FROM
vivo_theme_detail_info_pipeline
WHERE
(
package_id
) IN (
SELECT
t.package_id
FROM
(
SELECT
package_id
FROM
vivo_theme_detail_info_pipeline
GROUP BY
package_id
HAVING
count(1) > 1
) t
)
AND id NOT IN (
SELECT
dt.minid
FROM
(
SELECT
min(id) AS minid
FROM
vivo_theme_detail_info_pipeline
GROUP BY
package_id
HAVING
count(1) > 1
) dt
)
中文按首字母排序
SELECT
author
FROM
vivo_theme_detail_info_pipeline
GROUP BY
author
ORDER BY
CONVERT(author USING gbk)
Mybatis collection （一对多）1分页
// mybatis 使用collection标签实现一对多查询(多分页使用)
https://blog.csdn.net/wszcy199503/article/details/77867990
# code:
<resultMap id="authorDayMap"
type="com.colorfulworld.dataanalysis.domain.vivotheme.VivoThemeAuthorDays">
<id property="name" column="name" />
<result property="author" column="author"/>
<result property="modifyTime" column="modify_time" />
<result property="thumbPath" column="thumb_path" />
<result property="urlRoot" column="url_root" />
<result property="start" column="start"></result>
<result property="end" column="end"></result>
<collection property="details" column="{author=author, start=start, end=end, name=name}" ofType="authorDayMapKid" javaType="ArrayList" select="getAuthorDayMapKid">
<!-- <result property="oneday" column="oneday"></result>-->
<!-- <result property="downloads" column="downloads"></result>-->
</collection>
</resultMap>
<resultMap id="authorDayMapKid" type="com.colorfulworld.dataanalysis.domain.vivotheme.DadDownload">
<result property="oneday" column="oneday"></result>
<result property="downloads" column="downloads"></result>
</resultMap>
<select id="getAuthorDayMap"
resultMap="authorDayMap">
SELECT
author,
package_id,
theme_type,
`name`,
oneday,
thumb_path,
url_root,
(FROM_UNIXTIME( modify_time / 1000, '%Y-%m-%d %h:%i:%s' )
) AS modify_time,
#{start} as start,
#{end} as `end`
FROM
vivo_theme_detail_info_pipeline
where DATE_FORMAT((FROM_UNIXTIME(modify_time / 1000,'%Y-%m-%d %h:%i:%s')),'%Y-%m-%d') between #{start} and #{end}
<if test="author != null and author != ''">
and author = #{author}
</if>
GROUP BY
author,
`name`
ORDER BY
modify_time DESC
</select>
<select id="getAuthorDayMapKid"
resultMap="authorDayMapKid">
SELECT
oneday,
max(downloads) as downloads
FROM
vivo_theme_detail_info_pipeline
where author = #{author} and name=#{name} and oneday between #{start} and #{end}
GROUP BY
author,
`name`,
oneday
ORDER BY
modify_time DESC, oneday
</select>
List<VivoThemeAuthorDays> getAuthorDayMap(@Param("author")String author, @Param("start")String start, @Param("end")String end);
多字段键插入或更新
<insert id="saveOrUpdateDuplicate" parameterType="com.cht.modules.t.entity.IncomeEntity">
insert into
<include refid="BASE_TABLE"/>
<trim prefix="(" suffix=")" suffixOverrides=",">
excel_id, `date`, `name`, exposure_count, click_count, download_count, order_count, money, filename, update_time
</trim>
<trim prefix="values(" suffix=")" suffixOverrides=",">
#{income.excelId}, #{income.date}, #{income.name}, #{income.exposureCount}, #{income.clickCount}, #{income.downloadCount},
#{income.orderCount}, #{income.money}, #{income.filename}, now()
</trim>
on **duplicate key** update excel_id=values(excel_id), `date`=values(`date`), exposure_count=values(exposure_count),
click_count=values(click_count), download_count=values(download_count), order_count=values(order_count), money=values(money), update_time=values(update_time)
</insert>
SQL优化Explain
// title: sql优化的30个建议 https://www.cnblogs.com/xc-chejj/p/11244748.html
explain select ... 可以查看sql具体执行了多少行，用没用到索引
// title: explain 详解 https://www.cnblogs.com/qlqwjy/p/7767479.html
// title: 知乎优化建议 https://zhuanlan.zhihu.com/p/130820841
// explain详解
https://blog.csdn.net/youanyyou/article/details/109664306
// mysql 执行顺序
https://www.cnblogs.com/loong-hon/p/13470263.html
获取分组后的第一个数据
substring_index(substring_index(GROUP_CONCAT(tt.url_root),',',3),',',-1) AS url_root
Mysql的一个莫名其妙的问题(版本问题)
问题描述：
项目：彩虹糖财务报表管理系统
问题：一个2w row的表需要自连接，代码如下：
SELECT
t1.*, ROUND(
(
t1.money - IFNULL(t2.money, 0)
) * 0.665,
2
) AS reduce_money,
Round(
(
t1.money - IFNULL(t2.money, 0)
),
2
) AS reward
FROM
(
SELECT
c.`name` designerName,
a.filename,
a.artwork_id,
a.income_record_id,
b.`name`,
a.money money
FROM
t_income a
RIGHT JOIN t_artwork b ON a.artwork_id = b.id
RIGHT JOIN t_designer c ON b.designer_id = c.id
AND c.need_statistics = 0
WHERE
a.filename = '2021-08'
AND b.firm = 'vivo'
) t1
LEFT JOIN (
SELECT
c.`name` designerName,
a.filename,
a.artwork_id,
a.income_record_id,
b.`name`,
a.money money
FROM
t_income a
RIGHT JOIN t_artwork b ON a.artwork_id = b.id
RIGHT JOIN t_designer c ON b.designer_id = c.id
AND c.need_statistics = 0
WHERE
a.filename = '2021-07'
AND b.firm = 'vivo'
) t2 ON t1.artwork_id = t2.artwork_id
本地mysql和远程mysql的效率完全不同，本地需要40s+，远程0.02s
问题探究：
使用explain 查看两个表执行计划
本地：
1 SIMPLE c ALL PRIMARY 29 10 Using where
1 SIMPLE b ref PRIMARY,uni uni 5 profit_report.c.id 14 10 Using index condition
1 SIMPLE a ref uni_ uni_ 5 profit_report.b.id 252 10 Using where
1 SIMPLE c ALL 29 100
1 SIMPLE b ref uni uni 5 profit_report.c.id 14 100 Using where
1 SIMPLE a ref uni_ uni_ 5 profit_report.b.id 252 100 Using where
远程：
1 SIMPLE c ALL PRIMARY 29 10 Using where
1 SIMPLE b ref PRIMARY,uni uni 5 profit_report.c.id 14 10 Using index condition
1 SIMPLE a ref uni_ uni_ 5 profit_report.b.id 252 10 Using where
1 SIMPLE b eq_ref PRIMARY,uni PRIMARY 4 profit_report.b.id 1 100 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 profit_report.b.designer_id 1 100 Using where
1 SIMPLE a ref uni_ uni_ 5 profit_report.b.id 252 100 Using where
可以看出是在4、5行出现了偏差，但是不知道问题出在哪里，于是猜测是配置文件的问题，结果好像也不是，因为也看不太懂
然后查看mysql版本后发现，本地mysql版本为 8.0.12， 远程的mysql版本为 8.0.20， 最后自己做了一个对照实验，发现的确
是版本问题。
问题仍未解决。
Group_concat函数返回最大长度
show variables like 'group_concat_max_len';
// 默认是1024，如果拼接字太长就会导致显示不全
// 生产环境修改值
// 1. 临时修改值
SET GLOBAL group_concat_max_len = 4294967295;
SET SESSION group_concat_max_len = 4294967295;
// 2. 写入my.ini/my.cnf
// windows是my.int, linux 是 my.cnf
在[mysqld]下新增配置：group_concat_max_len=4294967295
注意是：在[mysqld]下新增；在[mysqld]下新增；在[mysqld]下新增；重要的事情说三遍。
然后重启mysql服务，然后通过上面的查询sql进行查看是否生效。
// tips: linux 查看my.cnf位置,一般会查到多个，用cat看下，只有一个能用
mysqld --verbose --help|grep -A 1 'Default options'
// or
[arlin@MiWiFi-R1CM-srv ~]$ which mysqld
/usr/sbin/mysqld
[arlin@MiWiFi-R1CM-srv ~]$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
2017-10-12T11:09:22.209992Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-10-12T11:09:22.210068Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
[arlin@MiWiFi-R1CM-srv ~]$
Mysql 日期取年月日
date_format(date ,'%Y-%m-%d' )
Mysql插入时判断记录是否存在
insert into tunnel_alarm
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="targetId != null">target_id,</if>
<if test="alarmType != null">alarm_type,</if>
<if test="targetType != null">target_type,</if>
<if test="alarmTime != null">alarm_time,</if>
<if test="lastAlarmTime != null">last_alarm_time,</if>
<if test="alarmPosition != null">alarm_position,</if>
<if test="createTime != null">create_time,</if>
<if test="updateTime != null">update_time,</if>
<if test="updateTime != null">read_state,</if>
</trim>
<trim prefix="select " suffix="" suffixOverrides=",">
<if test="targetId != null">#{targetId},</if>
<if test="alarmType != null">#{alarmType},</if>
<if test="targetType != null">#{targetType},</if>
<if test="alarmTime != null">#{alarmTime},</if>
<if test="lastAlarmTime != null">#{lastAlarmTime},</if>
<if test="alarmPosition != null">#{alarmPosition},</if>
<if test="createTime != null">#{createTime},</if>
<if test="updateTime != null">#{updateTime},</if>
<if test="updateTime != null">#{readState},</if>
</trim>
from dual
where not exists (select alarm_id from tunnel_alarm
<where>
<if test="targetId != null "> and target_id = #{targetId}</if>
<if test="alarmType != null "> and alarm_type = #{alarmType}</if>
<if test="targetType != null "> and target_type = #{targetType}</if>
and read_state = 0
</where>
)
Mybatis插入时返回主键
<insert id="insertDept" **keyProperty="deptId"** parameterType="Dept">
insert into tunnel_dept
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="deptId != null">dept_id,</if>
<if test="deptName != null and deptName != ''">dept_name,</if>
<if test="createTime != null">create_time,</if>
<if test="delFlag != null">del_flag,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="deptId != null">#{deptId},</if>
<if test="deptName != null and deptName != ''">#{deptName},</if>
<if test="createTime != null">#{createTime},</if>
<if test="delFlag != null">#{delFlag},</if>
</trim>
</insert>
MYSQL 批量修改 自增
set @row=0;
update tb_s
set a = (
select @row := @row + 1 as nid
)
where 1 = 1
误删数据恢复
// 参考： 通过二进制日志恢复误删数据库 http://t.zoukankan.com/plutozzl-p-13530442.html
// mysqlbinlog 工具分析binlog日志 https://www.cnblogs.com/lvzf/p/10689462.html
// mysql误删数据快速恢复 https://www.cnblogs.com/-mrl/p/9959365.html
mysqlbinlog --no-defaults --database=profit_report **--base64-output=decode-rows -v** --start-datetime="2021-12-01 17:08:05" --stop-datetime="2021-12-03 00:00:00" ./binlog.000041 > pr-rollback.txt
Java PoJo转sql表
GenerateSqlFromEntityUtil.java
回滚
MyIsam 不能回滚，
Innodb可以回滚
Mysql按指定顺序排序的两种写法
SELECT `ID` FROM wp_posts WHERE `ID` in (1,2,3,4,5) ORDER BY FIELD(`ID`,5,4,3,1,2); // 纯数字字段不用处理引号，比较好拼接
SELECT `ID` FROM wp_posts WHERE `ID` in (1,2,3,4,5) ORDER BY FIND_IN_SET(`post_title`,'A,B,C,D,E'); // 一个引号全包住，搞定字符值字段
-- 注意：第一个参数不能是字符串，否则不起作用
-- 性能差异：
# 方法1
SELECT
*, a.material_num
FROM
wp_silkscreen_task_config a
ORDER BY
material_num IN (
(
SELECT
material_num
FROM
wp_silkscreen_task_config
WHERE
state <> 2
GROUP BY
material_num
HAVING
min(state) > 0
)
)
# 方法2
SELECT
*, a.material_num
FROM
wp_silkscreen_task_config a
ORDER BY
FIND_IN_SET(
`material_num`,
(
SELECT
material_num
FROM
wp_silkscreen_task_config
WHERE
state <> 2
GROUP BY
material_num
HAVING
min(state) > 0
)
)
mysql8.0版本 the user specified as a definer ('root'@'%') does not exist问题解决
mysql> create user 'root'@'%' identified by '密码';
Query OK, 0 rows affected (2.35 sec)
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.06 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
mysql8.0 zip安装
1. mysqld --initialize --console # 前提要有my.ini, 初始密码会打印到控制台
2. mysqld install
3. mysql -u root -p # 输入刚刚的初始密码
4. alter user 'root'@'localhost' identified by 'newpassword'; #修改密码
浙公网安备 33010602011771号