大数据之数据库mysql优化实战（一）

最新推荐文章于 2024-09-09 14:40:58 发布

weixin_34358092

最新推荐文章于 2024-09-09 14:40:58 发布

阅读量108

点赞数

文章标签：数据库大数据 python

原文链接：https://my.oschina.net/budaoniu/blog/647361

版权

2019独角兽企业重金招聘Python工程师标准>>>

:facepunch:

大数据之数据库mysql优化实战（一）

首先你要有数据，不然怎么测试，几百条就算了，还没跑就完了。

本章主题如何向数据库插入10万条数据

创建数据库

CREATE TABLE `etcblacklist11` (
  `ID` int(24) NOT NULL AUTO_INCREMENT,
  `IssuerID` varchar(16) DEFAULT NULL,
  `NetNo` varchar(4) DEFAULT NULL,
  `CARDID` varchar(16) DEFAULT NULL,
  `ObuID` varchar(16) DEFAULT NULL,
  `License` varchar(25) DEFAULT NULL,
  `strID` varchar(2) DEFAULT NULL,
  `strTmp1` varchar(3) DEFAULT NULL,
  `strtime1` datetime DEFAULT NULL,
  `strTmp2` varchar(3) DEFAULT NULL,
  `strtime2` datetime DEFAULT NULL,
  `strTmp3` varchar(3) DEFAULT NULL,
  `strTmp4` varchar(3) DEFAULT NULL,
  `LicenseName` varchar(4) DEFAULT NULL,
  `LicenseNameA` varchar(4) DEFAULT NULL,
  `LicenseNameB` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

书写存储过程(create procedure procefureName())

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN   
    DECLARE IDs INT(11);   
    DECLARE i INT;   
    DECLARE CARDID VARCHAR(16);  
    DECLARE IssuerID VARCHAR(16);  
    DECLARE ObuID VARCHAR(16);  
    DECLARE License VARCHAR(8);  
    DECLARE strID VARCHAR(4);  
    DECLARE strTmp1 VARCHAR(4);  
    DECLARE strTmp2 VARCHAR(4);  
    DECLARE strTmp3 VARCHAR(4);  
    DECLARE NetNo VARCHAR(4);  
    DECLARE LicenseName VARCHAR(2);  
    DECLARE LicenseNameA VARCHAR(2);  
    DECLARE LicenseNameB VARCHAR(2);  
    SET IDs =11;  
    SET LicenseName = '京';  
    SET strID = RIGHT(CONCAT('00',IDs),2);  
    SET i=0;   
    set strTmp1 = '1';  
    SET strTmp1 = CONCAT(strID,'1');  
    SET strTmp2 = CONCAT(strID,'2');  
    SET strTmp3 = CONCAT(strID,'3');  
    SET NetNo = CONCAT(strID,'01');  
    SET LicenseNameA = CONCAT(LicenseName,'A');  
    SET LicenseNameB= CONCAT(LicenseName,'B');  
      
    WHILE i<24315 DO   
    SET CARDID  = CONCAT(strTmp1,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));  
    SET IssuerID    = CONCAT(strTmp2,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));  
    SET ObuID   = CONCAT(strTmp3,RIGHT(CONCAT('0000000000000',cast(i as char(16))),13));  
    SET License = CONCAT(LicenseNameA,RIGHT(CONCAT('00000',cast(i as char(16))),5));  
    #SET CARDID = CONCAT('0847',cast(i as char(12)));  
    INSERT INTO `ETCBlackList11`(IssuerID, NetNo, CARDID, ObuID, License,strID,strTmp1,strtime1,strTmp2,strtime2,strTmp3,strTmp4,LicenseName,LicenseNameA,LicenseNameB) VALUES (IssuerID, NetNo, CARDID, ObuID, License, '0', '0', '2000-01-01 00:00:00', '1', '2000-01-01 00:00:00', '测试', '16019', '0', '0', '0');  
    SET i = i + 1;   
    END WHILE;   
        
    END