MySQL学习记录

一、MySQL安装

链接 https://www.cnblogs.com/duanrantao/p/8988116.html

https://www.cnblogs.com/jessica-test/p/9047431.html

二、MySQL语法

三、MySQL应用

1、mysql插入大数据（100W+）-存储过程

（链接--https://www.cnblogs.com/nwgdk/p/10889985.html）

方法：内存表添加->内存表数据复制到普通表

步骤：

A # 内存表

 1 DROP TABLE IF EXISTS `t_name_memory_table`;
 2 
 3 CREATE TABLE `t_name_memory_table` (
 4 
 5   `id` int(11) NOT NULL AUTO_INCREMENT,
 6 
 7   `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
 8 
 9   `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
10 
11   `create_time` datetime(0) NULL DEFAULT NULL,
12 
13   `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING HASH
14 
15 ) ENGINE = MEMORY AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Fixed STORAGE MEMORY;
16 
17 SET FOREIGN_KEY_CHECKS = 1;

View Code

B # 普通

 1 DROP TABLE IF EXISTS `t_name_table`;
 2 
 3 CREATE TABLE `t_name_table` (
 4 
 5   `id` int(11) NOT NULL AUTO_INCREMENT,
 6 
 7   `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
 8 
 9   `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
10 
11   `create_time` datetime(0) NULL DEFAULT NULL, `update_time` datetime(0) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE
12 
13 ) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
14 
15 SET FOREIGN_KEY_CHECKS = 1;

View Code

C #函数

 1 CREATE DEFINER=`root`@`localhost` FUNCTION `rand_str`(n INT) RETURNS varchar(255) CHARSET utf8
 2 
 3 BEGIN
 4 
 5     DECLARE i INT DEFAULT 0;
 6 
 7     DECLARE char_str VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
 8 
 9     DECLARE return_str VARCHAR(255) DEFAULT '';
10 
11     WHILE i < n DO
12 
13         SET return_str = CONCAT(return_str,substring(char_str, FLOOR(1 + RAND()*62), 1));
14 
15         SET i = i + 1;
16 
17     END WHILE;
18 
19     RETURN return_str;
20 
21 END

View Code

D #存储过程

 1 CREATE DEFINER=`root`@`localhost` PROCEDURE `add_data`(IN `n` int)
 2 
 3 BEGIN
 4 
 5     DECLARE i INT DEFAULT 0;
 6 
 7     WHILE i < n DO
 8 
 9         INSERT INTO test_memory(username,`password`,create_time,update_time) VALUES (rand_str(8),MD5('123456'),NOW(),NOW());
10 
11         SET i = i + 1;
12 
13     END WHILE;
14 
15 END

View Code

E #插入数据eg.

1   -- 调用存储过程
2 
3      CALL add_data(1000000);
4 
5   -- 将内存表数据复制到普通表
6 
7      INSERT INTO t_name_tableSELECT * FROM t_name_memory_table;

View Code