mysql

MySQL使用记录

Posted by     "zengchengjie" on Thursday, February 10, 2022

部署

docker部署

dockerhub上有很多MySQL版本，这里我们选择常用的 MySQL5.7版本

  • 拉取镜像：

    # docker pull mysql:5.7.38
5.7.38: Pulling from library/mysql
c1ad9731b2c7: Pull complete
54f6eb0ee84d: Pull complete
cffcf8691bc5: Pull complete
89a783b5ac8a: Pull complete
6a8393c7be5f: Pull complete
af768d0b181e: Pull complete
810d6aaaf54a: Pull complete
81fe6daf2395: Pull complete
5ccf426818fd: Pull complete
68b838b06054: Pull complete
1b606c4f93df: Pull complete
Digest: sha256:7e99b2b8d5bca914ef31059858210f57b009c40375d647f0d4d65ecd01d6b1d5
Status: Downloaded newer image for mysql:5.7.38
docker.io/library/mysql:5.7.38

  • 查看docker镜像：

    # docker images
REPOSITORY   TAG       IMAGE ID       CREATED       SIZE
mysql        5.7.38    2a0961b7de03   2 weeks ago   462MB

  • 创建容器内在本地的映射文件夹：

    mkdir -p /home/mysql/data /home/mysql/logs /home/mysql/conf

  • 初始化MySQL的配置文件

    touch /home/mysql/conf/my.cnf

  • 创建docker容器并启动，将数据，日志，配置文件映射到本机

    docker run -p 3306:3306 --name mysql -v /home/mysql/conf:/etc/mysql/conf.d -v /home/mysql/logs:/var/log/mysql -v /home/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.38

  • 或者我们可以使用docker-compose.yml启动

    version: '3'
services:
  db:
    image: 'docker.io/mysql:5.7.38' #使用的镜像
    restart: always
    command: --lower_case_table_names=1 #区分大小写
    container_name: mysql  #容器名
    volumes:
      - ./data:/var/lib/mysql  #挂载目录，持久化存储
    ports:
      - '3306:3306'
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: "123456"   #设置root用户的密码

  • 查看容器是否启动

    # docker ps
CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
4d1f3a7431f0   mysql:5.7.38   "docker-entrypoint.s…"   16 seconds ago   Up 15 seconds   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

  • 查看本地文件夹内容：

    其中，data文件夹已经有内容了

    # ls /home/mysql/
conf  data  logs
[root@localhost ~]# ls /home/mysql/conf/
[root@localhost ~]# ls /home/mysql/data/
auto.cnf    client-cert.pem  ibdata1      ibtmp1              private_key.pem  server-key.pem
ca-key.pem  client-key.pem   ib_logfile0  mysql               public_key.pem   sys
ca.pem      ib_buffer_pool   ib_logfile1  performance_schema  server-cert.pem
[root@localhost ~]# ls /home/mysql/logs

常用命令

  • 进入MySQL命令行界面

    docker exec -it mysql /bin/bash
mysql -uroot -p123456

  • 查看当前使用引擎

    # 查看当前引擎
show engines;
# 查看默认引擎
show variables like '%storage_engine%';
# 查看字符集
show variables like '%character%';
# 查看字符集
show variables like 'collation%';
# 查看MySQL所支持的字符集
show charset;
#显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
show table status;
# 查看表的字符集
show table status from 库名 like  表名;
# 查看数据库
show databases;
#显示系统中正在运行的所有进程，也就是当前正在执行的查询。
show processlist;
#显示表中列信息
show columns from table_name from database_name;
show columns from database_name.table_name;
#显示一个用户的权限，显示结果类似于grant 命令
show grants for user_name@localhost;
#显示表中索引信息
show index from table_name;
#显示一些系统特定资源的信息，例如，正在运行的线程数量
show status;
#显示系统变量的名称和值
show variables;
#显示服务器所支持的不同权限
show privileges;

  • 登录

    mysql -u userName -p databaseName  -h172.1.0.8

  • dump数据

    #dump数据
docker exec -it  mysql mysqldump -uroot -p123456 inm_user > ./inm_user.sql
docker exec -i  mysql permission < ./permission.sql
#拷贝数据
docker cp permission.sql mysql:/home
#进入mysql命令行
docker exec -it mysql mysql -uroot -p123456
#创建数据库
create database permission default character set utf8mb4 collate;
#选择数据库
use permission;
#导入dump的数据库文件数据
source /home/permission.sql;

sql语句

插入数据

INSERT INTO test VALUES (1,'b4','c4') ON DUPLICATE KEY UPDATE b=VALUES(b),c=VALUES(c);

更新数据

UPDATE table_1
INNER JOIN table_2 on table_1.`name` = table_2.user_name
SET table_1.user_id = table_2.user_id
WHERE table_1.created_at>table_2.created_at；

UPDATE table_1 SET table_1.user_id = table_2.user_id WHERE table_1.created_at>table_2.created_at；

删除数据

delete from table_1 where id = 1;

清空表格

-- 关闭外键检查，防止出现Cannot truncate a table referenced in a foreign key constraint...类似的报错
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

查询数据

select * from table_1;

修改表结构

使用after或者first字段决定字段的位置

增加字段

ALTER TABLE `server` ADD COLUMN ip INT(8) NOT NULL DEFAULT 0 COMMENT '服务器IP'AFTER `note`;

删除表字段

ALTER TABLE netline_wave DROP COLUMN `alert_time`;

修改字段类型

ALTER TABLE netline_wave MODIFY `title` VARCHAR(128);

删除索索引

索引

索引原理

  • Mysql索引概念： 说说Mysql索引，看到一个很少比如：索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

  • Mysql索引主要有两种结构：B+树和hash.

    • hash:hsah索引在mysql比较少用,他以把数据的索引以hash形式组织起来,因此当查找某一条记录的时候,速度非常快.当时因为是hash结构,每个键只对应一个值,而且是散列的方式分布.所以他并不支持范围查找和排序等功能.

    • B+树:b+tree是mysql使用最频繁的一个索引数据结构,数据结构以平衡树的形式来组织,因为是树型结构,所以更适合用来处理排序,范围查找等功能.相对hash索引,B+树在查找单条记录的速度虽然比不上hash索引,但是因为更适合排序等操作,所以他更受用户的欢迎.毕竟不可能只对数据库进行单条记录的操作.

索引相关sql

查询索引

show index from t_test

创建索引

  • 主键索引

    ALTER TABLE `table_name` ADD PRIMARY KEY (`column`)

  • 普通索引

    ALTER TABLE `table_name` ADD INDEX index_name ( `column`)

  • 唯一索引

    ALTER TABLE `table_name` ADD UNIQUE (`column`)

  • 组合/多列/联合索引

    ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

  • 联合唯一索引

    alter table t_aa add unique index(aa,bb);
//或者 
CREATE UNIQUE INDEX idx_aa_bb on t_aa(aa,bb);

    如果创建这个索引的时候，表中有历史数据存在重复记录，则可以执行以下语句：

    alter ignore table t_aa add unique index(aa,bb);

    或者执行类似以下sql:

    DELETE t1 FROM newip_addr_ping t1
	INNER JOIN newip_addr_ping t2
WHERE
	t1.id < t2.id
AND t1.ip_address = t2.ip_address AND t1.creator_time = t2.creator_time;

  • 全文索引

    ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

还有一种情况就是,我们需要为以前的表 创建这个索引,有可能以前的数据中存在重复的记录 那怎么办呢? alter ignore table t_aa add unique index(aa,bb); 它会删除重复的记录（会保留一条），然后建立唯一索引，高效而且人性化。

删除索引

  • 删除

    DROP INDEX server_id_time ON netline_wave;

索引优化

存储过程和事件

mysql存储过程

  • 创建存储过程

    DELIMITER $$
CREATE  PROCEDURE `deleteNetprobeTables`()
begin
    declare i int;
    set i=1;
    while i<=2 do
      set @tb = CONCAT('TRUNCATE TABLE netprobe',i);
        PREPARE create_stmt FROM @tb;
        EXECUTE create_stmt;
              set i=i+1;
    end while;
end $$;
DELIMITER ;

  • 调用存储过程

    call deleteNetprobeTables();

  • 查看存储过程

    show procedure status;

  • 删除存储过程

    drop PROCEDURE deleteNetprobeTables;

  • 复杂存储过程示例

    存储过程自动创建表分区(按时间)

    CREATE PROCEDURE proc_Add_Date_Partition_For_Table(IN BeginDate DATETIME, IN EndDate DATETIME, IN DBName VARCHAR(20),IN TableName VARCHAR(50))
  BEGIN
    DECLARE PartitionName varchar(50);
    DECLARE PartitionColumn varchar(50);
    DECLARE PartitionValue Datetime;
    if EndDate is not null then
        select PARTITION_NAME ,REPLACE(PARTITION_EXPRESSION,'`','') as PARTITION_COLUMN,
        str_to_date(SUBSTR(PARTITION_DESCRIPTION FROM 2 FOR 10),'%Y-%m-%d') as PARTITION_VALUE
        into PartitionName,PartitionColumn,PartitionValue
        from INFORMATION_SCHEMA.PARTITIONS where TABLE_SCHEMA=DBName and table_name=TableName
        order by PARTITION_ORDINAL_POSITION desc limit 1;
        IF PartitionValue IS NOT NULL THEN
        SET BeginDate = PartitionValue;
        END IF;

        WHILE BeginDate <= EndDate DO
        set @SqlStr=concat('alter table ',DBName,'.',TableName,
        ' add partition(partition p_',
        DATE_FORMAT(BeginDate,'%Y%m%d'),' values less than('',DATE_FORMAT(DATE_ADD(BeginDate,INTERVAL 1 day),'%Y-%m-%d'),''));');

        PREPARE STMT FROM @SqlStr;
        EXECUTE STMT;   

        SET BeginDate = DATE_ADD(BeginDate,INTERVAL 1 day);
        END WHILE;
    end if;
END;

mysql事件

  • 打开事件开关

    SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = 1;

  • 创建事件

    事件定时类型可以有多种选择，如每季度、每月、每天、定时启动、延时启动、从某个时间点开始定时启动等

     CREATE EVENT delete_table_schedule ON SCHEDULE AT '2021-09-01 00:00:00' + INTERVAL 22 DAY do call deleteNetprobeTables();
 CREATE EVENT delete_table_schedule ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY do call deleteNetprobeTables();

  • 查看事件

    SHOW events;

  • 删除事件

    DROP EVENT delete_table_schedule;

  • 查看当前时间

    SELECT CURRENT_TIMESTAMP

函数

常用函数

  • 随机生成IP

    SELECT
	CONCAT(
      TRUNCATE (RAND() * 255 + 1, 0),
      '.',
      TRUNCATE (RAND() * 255 + 1, 0),
      '.',
      TRUNCATE (RAND() * 255 + 1, 0),
      '.',
      TRUNCATE (RAND() * 255 + 1, 0)
	)

  • 随机生成mac

    SELECT
	CONCAT_WS(
      ':',
      substring(MD5(RAND()),1,2),
      substring(MD5(RAND()),1,2),
      substring(MD5(RAND()),1,2),
      substring(MD5(RAND()),1,2),
      substring(MD5(RAND()),1,2),
      substring(MD5(RAND()),1,2)
	);

  • 随机生成两位数

    SELECT TRUNCATE ((RAND() * 90 + 10), 0)

  • 随机两位字符

    SELECT LEFT (uuid(), 2);

自定义函数

日期函数

日期类型的 default 设置

类型 字节 格式 用途 是否支持设置系统默认值
date 3 YYYY-MM-DD 日期值 不支持
time 3 HH:MM:SS 时间值或持续时间 不支持
year 1 YYYY 年份 不支持
datetime 8 YYYY-MM-DD HH:MM:SS 日期和时间混合值 不支持
timestamp 4 YYYYMMDD HHMMSS 混合日期和时间，可作时间戳 支持

关于 default 设置，通常情况下会使用当前时间作为默认值。

Example:

ts_time timestamp NOT NULL DEFAULT NOW();

或者

ts_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP();

根据上表可知，除了 timestamp 类型支持系统默认值设置，其他类型都不支持。

如果建表语句中有:

ts_time1 time NOT NULL DEFAULT NOW();
ts_time3 year NOT NULL DEFAULT NOW();
ts_time2 date NOT NULL DEFAULT CURRENT_TIMESTAMP();
ts_time2 datetime NOT NULL DEFAULT CURRENT_TIMESTAMP();

都会报错。所以想要设置某个日期列的默认值为当前时间，只能使用 timestamp 类型，并设置 DEFAULT NOW() 或 DEFAULT CURRENT_TIMESTAMP() 作为默认值。

常见的日期函数

函数 描述
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间按的单独部分
DATE_ADD() 给日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT() 用不同的格式显示日期/时间
CURRENT_TIME() 同CURTIME()
CURRENT_DATE 同CURDATE()
CURRENT_TIMESTAMP 返回当前的时间和NOW()相同

where 和 having区别

1、having用于groyup by之后。 2、where是用于表中筛选查询，having用于在where和group 结果中查询。 3、havin可以使用聚合函数，而where 不能。 4、having执行顺序位于where之后。

查询案例记录

  1. 查询重复记录，去重，并选择最新数据
SELECT `cmd` FROM cmd_info ci WHERE EXISTS
(SELECT 1 FROM cmd_info WHERE cmd=ci.cmd AND id < ci.id) ORDER BY id desc LIMIT 50

涉及到知识点：exists/not exists select 1

  • 原理

    以exist为例：先执行外层，再把外层的数据传到内层的sql，如果满足内层的sql条件，则外层保留数据

    Not exists相反

    select 1表示满足条件，其实除了1，其他字符也可以代替，select * select 2、3、4都是一样

  • 使用UNION连接两个查询结果，查询条数需要一致

常见问题

  • 升级问题

8.0升级8.0.30后报错：

This redo log was created with MySQL 8.0.27, and it appears logically non empty

删除ib_logfile0 ib_logfile1两个文件即可，删除前备份文件

  • 无法登录问题：
先把mysql配置为免密登录，然后进入mysql执行
USE mysql;
ALTER USER 'root'@'%' IDENTIFIED BY 'dev57vwnAjPK&UNE' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'dev57vwnAjPK&UNE';
flush privileges;
  • 执行数据插入的问题：
### Cause: java.sql.SQLSyntaxErrorException: INSERT command denied to user 'root'@'106.120.101.58' for table 'sys_job_log'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: INSERT command denied to user 'root'@'106.120.101.58' for table 'sys_job_log'

​ 原因：没有插入权限

​ 解决方案：修改root权限，如下图所示 执行：

SELECT * FROM mysql.user;
FLUSH PRIVILEGES;

​ 将对应的权限修改为Y即可：

​ 参考链接

Dbeaver 链接异常问题

DBeaver连接mysql时Public Key Retrieval is not allowed错误解决附图片

问题 Public Key Retrieval is not allowed

解决 在新建连接的时候，驱动属性里设置 allowPublicKeyRetrieval 的值为 true。

CATALOG
    FEATURED TAGS
    docker dubbo flink go-micro golang istio java java基础 jumpserver k8s kafka linux maven mongodb mybatis mycat mysql redis socket springboot springcloud sqlite websocket 并发 微服务 性能调优 测试 算法 虚拟机