MYSQL的相关问题集锦

黄土高坡上的独孤前辈

已于 2024-02-27 14:15:37 修改

阅读量519

点赞数

分类专栏： Mysql 文章标签：数据库开发

于 2022-02-17 17:23:28 首次发布

本文链接：https://blog.csdn.net/lihuazaizheli/article/details/122987992

版权

Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

0.MySQL基础知识
1.MYSQL的DATEFORMAT/SUBSTRING索引失效问题
2.MySQL 关键字和保留字导致报错
3.MYSQL如何查看是否走索引
4.自增主键和联合主键同时存在，如何做upsert?
5. 数据编码问题
6.查看mysql binlog的格式
7.MySQL的Decimal与Double的区别
8.MySQL的时间格式转换
9.MySQL的5.X版本不支持row_number函数的解决
10.MySQL中百分比数值拼接
11.MySQL连接池问题
100.MySQL优化指南

0.MySQL基础知识

https://www.w3cschool.cn/mysql/mysql-alter.html

1.MYSQL的DATEFORMAT/SUBSTRING索引失效问题

在做数仓同步的时候，不要对时间字段进行操作，直接使用 where updatetime > … ，
否则索引失效问题

2.MySQL 关键字和保留字导致报错

3.MYSQL如何查看是否走索引

使用explain
在这里插入图片描述
例如构建唯一索引UNIQUE KEY t1 (student_id,name)
where student_id=123 --走索引
where student_id=123 or name=王者 --走索引
where name=王者 --不走索引

4.自增主键和联合主键同时存在，如何做upsert?

CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  student_id varchar ,
  name varchar,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t1` (student_id,name)
) ENGINE=InnoDB AUTO_INCREMENT=23105717 DEFAULT CHARSET=utf8mb4 COMMENT='测试'

此种情况下，通过唯一主键 UNIQUE KEY t1 (student_id,name)可实现upsert操作，但是如果statudent_id与name中有任意一个值为null，就会导致唯一主键的upsert时效，即会认为是重复数据，每插入一次就会新增一条数据。

查看 https://blog.csdn.net/qq_27198345/article/details/116382587

5. 数据编码问题

编码转换器 http://www.esjson.com/unicodeEncode.html

unicode转utf8,就能够将数据中的unicode转义成中文
在这里插入图片描述

6.查看mysql binlog的格式

biglog的三种格式：
https://blog.csdn.net/whatday/article/details/126297642

mysql的Binlog_row_image参数学习https://blog.csdn.net/weixin_41561862/article/details/114604508

7.MySQL的Decimal与Double的区别

https://www.begtut.com/mysql/mysql-decimal.html
decimal详细介绍
 MySQL bigint(20)，int(11)是什么意思

Mysql 为什么默认定义varchar(255) 而不是varchar(256)

默认decimal(10,0) 
普通保存2位小数 decimal(10,2)
int(11),bigint(20),varchar(255),decimal(10,0) ,text,json  都是默认长度

8.MySQL的时间格式转换

   --2023042421 转化为 2023-04-24 21:00:00
	select 
	DATE_FORMAT(str_to_date(date_time,'%Y%m%d%H%i'),'%Y-%m-%d %H:%i:%s')

9.MySQL的5.X版本不支持row_number函数的解决

参考《mysql 实现row_number() over(partition by ) 分组排序功能》
参考《MySQL的=与:=究竟有什么区别》

-- 按照id 分组，按照gmt_modified 降序排序开窗
-- 等同于 row_number() over(partition by id order by gmt_modified desc) as rank 
select 
* 
from 
(
select 
b.id
,b.gmt_modified,
if(@pdept=b.id,@rank:=@rank+1,@rank:=1) as rank, -- 如果当前分组编号和上一次分组编号相同，则@rank（对每一组的数据进行编号）值加1，否则表示为新的分组，从1开始
@pdept := b.id -- 必须定义变量@pdept用来保存上一次的分组id 
from 
(select * , @pdept := null ,@rank:=0 -- 初始化变量
from 
study_record where  `id`='16767'
ORDER BY `gmt_modified` desc  -- 该排序必须，否则结果会不对
) b
) c WHERE rank=1