#######################字符串####################
DROP TABLE if EXISTS `test_str`;
CREATE TABLE `test_str` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` BLOB NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
# VARCHAR必须指定长度,而其他的字符串不需要指定长度
#######################学习创建分区表##########################
INSERT INTO `db_jml_seventeen`.`test_key`(`id`, `name`, `age`, `time`) VALUES (0000000009, '222', 16, '2023-08-02 14:05:09');
INSERT INTO `db_jml_seventeen`.`test_key`(`id`, `name`, `age`, `time`) VALUES (0000000001, '1', 1, '2023-08-02 14:03:07');
INSERT INTO `db_jml_seventeen`.`test_key`(`id`, `name`, `age`, `time`) VALUES (0000000003, '2', 2, '2023-08-02 14:03:13');
INSERT INTO `db_jml_seventeen`.`test_key`(`id`, `name`, `age`, `time`) VALUES (0000000005, '3', 3, '2023-08-02 14:04:49');
INSERT INTO `db_jml_seventeen`.`test_key`(`id`, `name`, `age`, `time`) VALUES (0000000007, '11', 11, '2023-08-02 14:04:57');
#key分区比较特殊，虽然可支持范围比较大，但是非整数表达式就行分区时极易造成分区不均衡，实际操作时key分区能支持的极限就是字符串了，同时尽量避免使用key分区。
DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`time`)PARTITIONS 4;
SELECT * FROM `test_key` PARTITION(p0);
SELECT * FROM `test_key` PARTITION(p1);
SELECT * FROM `test_key` PARTITION(p2);
SELECT * FROM `test_key` PARTITION(p3);
# hash分区
DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`age`)PARTITIONS 4;
SELECT * FROM `test_hash` PARTITION(p3);
# list分区
DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by list(`age`)(
PARTITION p0 VALUES in (10,11),
PARTITION p1 VALUES in (20,21,11),
PARTITION p2 VALUES in (30,31))
# range分区
DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`name` VARCHAR(99) NOT NULL,
`age` int NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (30),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (50))
SELECT * FROM `test_range` PARTITION(p3);
# 实操
drop table if exists employees;
create table employees(
id int not null,
fname varchar(30),
lname varchar(30),
hired date not null default '1970-01-01',
separated date not null default '9999-12-31',
job_code int not null default 0,
store_id int not null default 0
)engine=innodb default charset=utf8
PARTITION by HASH(id)
PARTITIONS 5000;
partition by range(store_id)(
partition p0 values less than (6),
partition p1 values less than (11),
partition p2 values less than (16),
partition p3 values less than (21),
PARTITION p4 VALUES less than MAXVALUE
);
drop table if exists quarterly_report_status;
create table quarterly_report_status(
report_id int not null,
report_status varchar(20) not null,
report_updated TIMESTAMP not null
)
partition by range(unix_timestamp(report_updated))(
partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
partition p1 values less than (unix_timestamp('2008-04-01 00:00:00')),
partition p2 values less than (unix_timestamp('2008-07-01 00:00:00')),
partition p3 values less than (unix_timestamp('2008-10-01 00:00:00')),
partition p4 values less than (unix_timestamp('2009-01-01 00:00:00')),
partition p5 values less than (unix_timestamp('2009-04-01 00:00:00')),
partition p6 values less than (unix_timestamp('2009-07-01 00:00:00')),
partition p7 values less than (unix_timestamp('2009-10-01 00:00:00')),
partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
partition p9 values less than maxvalue
);
drop table if exists test;
create table test(
`id` int not null,
`report_status` varchar(20) not null,
`name` VARCHAR(32) DEFAULT null,
`time1` datetime,
`time2` TIMESTAMP,
PRIMARY key (`id`,`time1`),
INDEX `idx_name` (`name`) USING BTREE
)ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE= utf8_unicode_ci COMMENT '测试表'
PARTITION by RANGE(year(`time1`)*100+MONTH(`time1`))(
partition p0 values less than (202101),
partition p1 values less than (202102)
)
drop table if exists tbhash;
CREATE TABLE tbhash (
id INT ,
store_id INT,
name VARCHAR(32),
PRIMARY key(id,name)
)
PARTITION BY key(name)
PARTITIONS 4;
CREATE TABLE A(
id INT,
id1 INT,
id2 INT
)
CREATE TABLE B(
id1 INT,
name VARCHAR(10)
)
SELECT A.id, A.id1, A.id2,B1.`name` name1,B2.`name` name2
FROM A LEFT JOIN B B1 on A.id1 = B1.id
LEFT JOIN B B2 on A.id2 = B2.id WHERE A.id = 1;
drop TABLE test_null_index;
create procedure test_null_index(in num int)
BEGIN
DECLARE i int;
set i=1;
while (i<num)
DO
if mod(i,10)!=0 then
insert into test_null_index values (i,concat('aaa',i),null);
else
insert into test_null_index values (null,concat('aaa',i),'bbb');
end if;
set i=i+1;
END while;
END;
call test_null_index(100);
create index idx_test_null on test_null_index(id);
create index idx_test_null1 on test_null_index(name);
SELECT COUNT(name) FROM test_null_index;
explain SELECT * from test_null_index WHERE id is null;
explain SELECT * from test_null_index WHERE name is not null;
CREATE TABLE `test_null_index` (
`id` int(11) DEFAULT 0,
`mark` varchar(20) DEFAULT 'mark',
`name` varchar(11) DEFAULT 'name'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test_null_index values (1,null,null);
insert into test_null_index set id = 3;
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@测试@@@@
-- //测试语句
DROP TABLE IF EXISTS `test1`;
CREATE TABLE ` test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_surname` varchar(30) DEFAULT NULL,
`store_id` int(11) DEFAULT NULL,
`salesperson_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试'
PARTITION BY HASH (id) PARTITIONS 100;
call test_insert(1000)
CREATE DEFINER=`scan-test`@`%` PROCEDURE `test_insert`(in num int)
BEGIN
DECLARE i int;
set i=1;
while (i<num)
DO
if mod(i,10)!=0 then
insert into test1 values (i,concat('aaa',i),22,33);
else
insert into test1 values (i,concat('aaa',i),3,45);
end if;
set i=i+1;
END while;
END
-- //学习分区表时没学到家就开始研究，一心想用hash分区，但是hash分区必须传入一个整型参数，就开始自己研究函数；可惜不得要领，hash分区只能使用mysql内置函数，再者分区可以使用key分区。4种分区表其实已经完全满足日常需求了。
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `db_jml_seventeen`.`HashValue`(IN accountName CHAR(32))
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
practical_sql.zip （7个子文件） 
practical_sql 
common_sql.sql 6KB practice.sql 25KB createTable.sql 181KB optimize.sql 7KB lock_or_not.sql 5KB no_lock.sql 2KB slow_sql.sql 8KB资源评论
lipviolet
- 粉丝: 5888
最新资源
- update9-20250731.5.209.slice.img.7z.003
- update9-20250731.5.209.slice.img.7z.004
- 单相交错图腾柱PFC双闭环PI控制仿真实现与优化技巧
- update9-20250731.5.209.slice.img.7z.005
- 基于MATLAB的电流跟踪PWM控制技术：三相逆变器系统设计与仿真实现
- Spring Data JPA实现分页查询功能的完整示例
- 基于TMS320F28335的DSP移相程序：清晰逻辑，注释详尽，专业处理方波信号，开关频率达225kHz，支持后两路移相输出
- 自动驾驶Lattice规划算法详解：轨迹采样、评估与碰撞检测的Matlab和C++实现
- 电力电子领域三相四桥臂逆变器接非线性与不平衡负载的多准PR并联控制研究
- 基于INGO-BiLSTM与改进北方苍鹰优化算法的电力功率负荷预测模型及其超参数优化
- 基于Python的考试管理系统（试题管理 自动阅卷）
- STM32低成本简化版MD500E变频器与永磁同步电机控制算法核心代码解析
- 基于正负序分离技术的三电平NPC整流器不平衡电压控制模型预测与仿真研究
- elasticsearch ik-8 分词器
- 直齿轮六自由度平移-扭转耦合非线性动力学程序：时变压力角与齿侧间隙的影响分析 深度版
- Carsim与Simulink驾驶员在环实时仿真教程：cpar文件与联合仿真文件解析
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
