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 # 内存表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
B # 普通
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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;
C #函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
D #存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
E #插入数据eg.
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 -- 调用存储过程 2 3 CALL add_data(1000000); 4 5 -- 将内存表数据复制到普通表 6 7 INSERT INTO t_name_tableSELECT * FROM t_name_memory_table;