七三笔记

mysql创建表 mysql rename table mysql分库分表 mysql查看表 mysql时间 mysql测试数据初始化 mysql分页查询 mysql insert 参考文章

mysql创建表

 
CREATE TABLE students(
    id bigint(11)    NOT NULL AUTO_INCREMENT primary key COMMENT '主键',
    student_id    int(6) DEFAULT NULL COMMENT '学者ID',
    student_name  varchar(30) DEFAULT NULL COMMENT '学者名称',
    college_major varchar(15) DEFAULT NULL COMMENT '主攻方向',
    status        char(1),
    sumbit_data   datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '提交时间',
    score         double(25,3) NOT NULL
    );
    
alter table students add unique (student_name, status);

MySQL数字
INT： TINYINT（1个字节）、INT（4字节），BIGIN（8个字节），还有SMALLINT(2个节字)、MEDIUMINT（3个字节）
浮点：类型FLOAT,DOUBLE,定点类型DECIMAL
double(25,3) 表示总长度为25，包括3个小数点

 
create table fin_11 select * from fin where 1=2;

该方式创建的表上没有索引，不会将索引也带过来
新创建的表上无索引，连主键也没有

mysql rename table

 
mysql> rename table students_202211 to test;
Query OK, 0 rows affected (0.29 sec)
    

Server version: 8.0.32 MySQL Community Server - GPL 
千万级表测试，一瞬间就执行完了，并没有出现长时间锁表的情况

风险系数：高

 
这是个高风险操作：
本人曾经执行过一个大表rename(Server version: 8.0.32 MySQL Community Server - GPL)，
线上库表，rename很快执行完了，结果新表旧表都找不到了，
好在这库就我一个人用，不然就是大事故

所以，变更操作还得要有备份，并且准备恢复方案

mysql查看表

 
select table_name from sys.schema_table_statistics where table_schema='db1';

mysql时间

时间类型

 
DATE：格式为YYYY-MM-DD，范围1000-01-01到9999-12-31
TIME：格式为hhh:mm:ss
DATETIME：格式为YYYY-MM-DD hh:mm:ss，范围1000-01-01 00:00:00到9999-12-31 23:59:59；
TIMESTAMP：储存时间戳，格式与DATETIME相同，范围1970-01-01 00:00:01到2038-01-19 03:14:07；

TIMESTAMP 自动变换时区，在存储时会转成UTC时间，在取出时转换为服务器的所在时区的时间

CREATE TABLE test_date(
    id int(11) NOT NULL AUTO_INCREMENT primary key COMMENT '主键',
    num int,
    date1 DATE,
    time1 TIME,
    time_num TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    sumbit_data   datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '提交时间'
    );
mysql> select NOW(),CURDATE()
+---------------------+------------+
| NOW()               | CURDATE()  |
+---------------------+------------+
| 2023-02-07 11:28:00 | 2023-02-07 |
+---------------------+------------+
1 row in set (0.00 sec)
    
mysql> insert into test_date(num,date1,time1) values(1,CURDATE(),NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_date;
+----+------+------------+----------+---------------------+---------------------+
| id | num  | date1      | time1    | time_num            | sumbit_data         |
+----+------+------------+----------+---------------------+---------------------+
|  1 |    1 | 2023-02-07 | 11:29:32 | 2023-02-07 11:29:32 | 2023-02-07 11:29:32 |
+----+------+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

看上去没什么区别，推荐使用DATETIME

mysql> select NOW(),SYSDATE(),SLEEP(3),NOW(),SYSDATE();
+---------------------+---------------------+----------+---------------------+---------------------+
| NOW()               | SYSDATE()           | SLEEP(3) | NOW()               | SYSDATE()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2023-02-07 11:32:09 | 2023-02-07 11:32:09 |        0 | 2023-02-07 11:32:09 | 2023-02-07 11:32:12 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (3.00 sec)

mysql 字符串转日期

 
mysql> SELECT DATE_FORMAT("2023-02-07", "%Y-%m-%d") date_str;
+------------+
| date_str   |
+------------+
| 2023-02-07 |
+------------+
1 row in set (0.00 sec)

cast('2023-02-06 14:15:00' as datetime)
str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s')

mysql测试数据初始化

一次插入多笔数据

 
insert into students(student_id,student_name,college_major,status,sumbit_data,score) 
values('100001','耀然天成','海洋学','1',cast('2023-02-06 14:15:00' as datetime),88.5),
('100002','柯尔莫哥洛夫','数学，大气力学','1',str_to_date('2023-02-01 09:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100003','牛顿','物理学','1',str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100004','高斯','数学，天文学','1',str_to_date('2023-02-03 11:15:00','%Y-%m-%d %H:%i:%s'),100.0);
注意:
student_id本是int,插入的是字符串；
时间使用了cast与str_to_date两个方法

批量插入数据

 
create database vodb character set utf8;
create user 'automng'@'localhost' identified by 'rootroot';
GRANT ALL PRIVILEGES ON *.* TO 'automng'@'localhost'  WITH GRANT OPTION;

use vodb;
drop table if exists test;
create table  test ( tid int,tname varchar(12),test_id  int NOT NULL AUTO_INCREMENT COMMENT '主键',PRIMARY KEY (test_id),tvalue varchar(90)) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;
ALTER TABLE `test`
ADD COLUMN  `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;

use vodb;
DROP PROCEDURE IF EXISTS `p_addtest`;
DELIMITER ;;
CREATE DEFINER=`automng`@`localhost` PROCEDURE `p_addtest`(IN n int)
BEGIN
    DECLARE i int;
    SET i = 0;
    WHILE i < n DO
        insert into test(tid,tname,tvalue) values(i,'aaabbb',concat('有张有驰有分寸',i));
        set i = i + 1;
    END WHILE;
END
;;
DELIMITER ;

use vodb;
call p_addtest(5);


常用测试语句
select * from test limit 3;

select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.createtime = c.createtime limit 3;

select a.tvalue from vodb.test a,vodb.test b where a.tid = b.tid and a.createtime = b.createtime limit 3;

select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.createtime = c.createtime limit 3;

--180 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid limit 3;

--32 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid and a.tid > 30000 and a.tid < 35000 limit 3;

--6 sec
select a.tvalue from vodb.test a,vodb.test b,vodb.test c where a.tid = b.tid and a.tid = c.tid and a.tid > 30000 and a.tid < 31000 limit 3;

mysql分页查询


select * from students limit 3,7;

select * from students  order by id desc limit 3,7 ;

mysql insert

 
存在就跳过/忽略，即有唯一索引冲突时就不插入了
INSERT IGNORE INTO students (id) VALUES ('8'); 

存在就更新旧列，新列的值是多少都没有关系了，因为更新时没用到它
INSERT INTO students (id,score) VALUES (8,18) ON DUPLICATE KEY UPDATE score=score+1;

多值插入,经测试一次插入100W行记录没问题，约30个列
insert into students(student_id,student_name,college_major,status,sumbit_data,score) 
values('100001','耀然天成','海洋学','1',cast('2023-02-06 14:15:00' as datetime),88.5),
('100002','柯尔莫哥洛夫','数学，大气力学','1',str_to_date('2023-02-01 09:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100003','牛顿','物理学','1',str_to_date('2023-02-06 14:15:00','%Y-%m-%d %H:%i:%s'),100.0),
('100004','高斯','数学，天文学','1',str_to_date('2023-02-03 11:15:00','%Y-%m-%d %H:%i:%s'),100.0);

存在就先delete再insert, 不存在就直接插入，依据唯一索引，
REPLACE INTO ... 
如果有多个唯一索引，那么会delete所有唯一索引列对应的值后再删除，
意思是存在删除多行才插入一行的可能

参考文章

七三笔记路线：学习，记录，分享