VsNhDZWX

1When we store the order data in the database, the database will be bigger and bigger, so we should store the data day by day.
2
3The database sql  is as follow:
4
5
6how to split the order table by day
7
81, create procedure to create intraday order table:
9DELIMITER //
10DROP PROCEDURE IF EXISTS proc_test_date //
11CREATE PROCEDURE proc_test_date(IN cur_date_var VARCHAR(20))  
12BEGIN
13SET @date_temp = CONCAT('CREATE TABLE IF NOT EXISTS test_date_', cur_date_var, ' LIKE tb_test3');
14SELECT @date_temp;
15PREPARE stat_temp FROM @date_temp;
16EXECUTE stat_temp;
17DEALLOCATE PREPARE stat_temp;
18END //
19DELIMITER ;
20DELIMITER //
21DROP PROCEDURE IF EXISTS proc_test_date2 //
22CREATE PROCEDURE proc_test_date2()
23BEGIN
24DECLARE cur_date_var VARCHAR(20);
25SET cur_date_var = DATE_FORMAT(CURDATE(), '%Y%m%d');
26CALL proc_test_date(cur_date_var);
27END //
28DELIMITER ;
29CALL proc_test_date2();
30
312, crete event to call procedure every day
32CREATE EVENT event_test 
33ON SCHEDULE EVERY 1 DAY
34DO 
35CALL proc_test_date2();
36
37SHOW EVENTS;
38
393,create procedure to insert data
40
41DELIMITER //
42DROP PROCEDURE IF EXISTS proc_insert_order //`Events`
43CREATE PROCEDURE proc_insert_order(IN u VARCHAR(20), IN p VARCHAR(20))
44BEGIN
45DECLARE cur_date_var VARCHAR(20);
46SET cur_date_var = DATE_FORMAT(CURDATE(), '%Y%m%d');
47SET @queryVar = CONCAT('insert into test_date_', cur_date_var , ' (username, password) values (\'' ,u ,'\',\'' ,p ,'\')');
48SELECT @queryVar;
49PREPARE query_temp FROM @queryVar;
50EXECUTE query_temp;
51DEALLOCATE PREPARE query_temp;
52END //
53DELIMITER ;
54
55CALL proc_insert_order("water11", "pass11");
56
574,create procedure in query data
58
59DELIMITER //
60DROP PROCEDURE IF EXISTS proc_query_order //
61CREATE PROCEDURE proc_query_order()
62BEGIN
63DECLARE cur_date_var VARCHAR(20);
64SET cur_date_var = DATE_FORMAT(CURDATE(), '%Y%m%d');
65SET @queryVar = CONCAT('select * from test_date_', cur_date_var);
66PREPARE query_temp FROM @queryVar;
67EXECUTE query_temp;
68DEALLOCATE PREPARE query_temp;
69END //
70DELIMITER ;
71
72CALL proc_query_order();
73
74
75differen between index and key:
76
77CREATE INDEX index_user 
78ON tb_test7 (username)
79
80CREATE TABLE tb_test7(
81id INT(11),
82username VARCHAR(60),
83PASSWORD VARCHAR(60),
84PRIMARY KEY (id),
85KEY index_user (username)
86)