分区 触发器 存储过程

分区 触发器 存储过程

-- 分区
-- hash
CREATE TABLE p_hash(
id INT,
gid INT
)
PARTITION BY HASH(gid)
PARTITIONS 4;
-- key<!--more-->


CREATE TABLE p_key(
id INT,
gid INT
)
PARTITION BY KEY(gid)
PARTITIONS 3;

-- range
CREATE TABLE p_range(
id INT,
gid INT
)
PARTITION BY RANGE(gid)(
PARTITION p1 VALUES LESS THAN (1),
PARTITION p2 VALUES LESS THAN maxvalue
);

-- list
CREATE TABLE p_list(
id INT,
gid INT
)
PARTITION BY LIST(gid)(
PARTITION p1 VALUES IN (1),
PARTITION p2 VALUES IN (2)
);

-- 分表

SHOW TRIGGERS
-- 触发器
DELIMITER $
CREATE TRIGGER tar
AFTER
INSERT
ON p_hash
FOR EACH ROW
BEGIN
INSERT INTO p_list VALUES(1,2);
END$
DELIMITER ;

-- 存储过程

SHOW PROCEDURE STATUS;

DELIMITER $
CREATE PROCEDURE test()
BEGIN
DECLARE b INT DEFAULT '0';
WHILE b < 3 DO
SET b := b+1;
SELECT b;
END WHILE;
END$
DELIMITER ;

CALL test();
--