導入大SQL文件
[mysqld]
# 大批量導入優化
bulk_insert_buffer_size=1G
max_allowed_packet=1G
innodb_autoextend_increment=512M
innodb_buffer_pool_size=4G
innodb_log_buffer_size=4G
innodb_log_file_size=4G
動態行列轉換
DROP TABLE IF EXISTS tb_score;CREATE TABLE tb_score(id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT '用戶id',subject VARCHAR(20) COMMENT '科目',score DOUBLE COMMENT '成績',PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO tb_score(userid,subject,score) VALUES ('001','語文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','數學',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英語',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','語文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','數學',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英語',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','語文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','數學',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英語',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);
select * from test.tb_score;selectuserid,sum(if(subject='語文', score, 0)) as '語文',sum(if(subject='數學', score, 0)) as '數學',sum(if(subject='英語', score, 0)) as '英語'
from test.tb_score
group by userid;SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
遞歸
開發中經常會遇到樹形結構，例如菜單、分類等，我們通常需要在代碼中使用遞歸的方式組織樹形數據。
現在有一張課程分類表，需要查詢給定分類的所有子分類。
課程表如下：
create table course_category
(id varchar(20) not null comment '主鍵'primary key,name varchar(32) not null comment '分類名稱',label varchar(32) null comment '分類標簽默認和名稱一樣',parentid varchar(20) default '0' not null comment '父結點id（第一級的父節點是0，自關聯字段id）',is_show tinyint null comment '是否顯示',orderby int null comment '排序字段'
)comment '課程分類' charset = utf8;
- 現在系統中最多只有三級分類，可以直接進行兩次表關聯實現。
select a.name, b.name, c.name
from course_category ajoin course_category b on a.id = b.parentidjoin course_category c on b.id = c.parentid
where a.id = '1';
- 使用Mysql遞歸語法。
上面的方法顯然不夠通用，隨著分類層級的變化SQL也需要更改，在Mysql 8.0中提供了遞歸語法，輕松實現樹形結構的查詢。
with recursive t1 as (-- 遞歸的上下文變量select 1 as nunion all-- 遞歸的查詢，每次遞歸都會基于該sql的結果繼續，直到where條件不滿足停止遞歸。select n + 1 from t1 where n < 5
)
select * from t1;
執行上面的sql將會得到：
| n |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
想要查詢id='1’的分類（根節點）下所有的子分類的SQL如下：
with recursive t1 as (select * from course_category where id = '1'union allselect a.* from course_category a join t1 on t1.id = a.parentid
)
select * from t1;
)
)
