mysql添加测试数据

自动添加测试数据

DELIMITER $$
DROP PROCEDURE IF EXISTS fillTable $$
CREATE PROCEDURE fillTable(IN num INT  , IN tbName VARCHAR(16))
BEGIN 
-- 获取当前数据库
	SELECT (@dbName:=DATABASE());
	SET @tbName = tbName;
-- 获取表的字段总数
	SET @currSql = "select count(1) from information_schema.COLUMNS where table_name = ? and table_schema = ? into @columnSum";
	PREPARE stmt FROM @currSql;
	EXECUTE stmt USING @tbName , @dbName;
	DEALLOCATE PREPARE stmt;
	
	SET @currNum = 0;
	
-- 这里设置随机的字符串
	SET @chars = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
	
	WHILE @currNum < num DO 
		-- 这里设置sql后面拼接
		SET @insertSql = CONCAT("insert into " , @tbName , " values ( ");
		SET @columnNum = 1;
		WHILE @columnNum <= @columnSum DO
			SET @VALUE := '';
			SET @currSql = "select (@column := COLUMN_NAME) , (@length := CHARACTER_MAXIMUM_LENGTH) , (@key := COLUMN_KEY) , (@type := DATA_TYPE) from information_schema.COLUMNS where table_name = ? and table_schema = ? limit ?";
			PREPARE stmt FROM @currSql;
			EXECUTE stmt USING @tbName , @dbName , @columnNum;
			DEALLOCATE PREPARE stmt;
			SELECT @insertSql ;
	-- 根据类型来填充数据
			IF RIGHT(@TYPE , 3) = 'int' THEN
				IF @TYPE = 'int' THEN
					SET @VALUE =@currNum;
				ELSE 
					SET @VALUE = FLOOR(RAND() * 100);
				END IF;
			
			ELSEIF RIGHT(@TYPE , 4) = 'char' THEN
				SET @counter = 0;
				WHILE @counter < @LENGTH DO    
	        		 SET @VALUE = CONCAT(@VALUE,SUBSTR(@chars,CEIL(RAND()*(LENGTH(@chars)-1)),1));  
	    			SET @counter = @counter + 1;
	    		END WHILE;
	    		
	    		SET @VALUE = CONCAT("'" , @VALUE , "'");
	    		
	    	ELSEIF @TYPE = 'blob' OR RIGHT(@TYPE , 4) = 'text' THEN
	    		SET @counter = 0;
	    	 	WHILE @counter < 100 DO    
	        		SET @VALUE = CONCAT(@VALUE,SUBSTR(@chars,CEIL(RAND()*(LENGTH(@chars)-1)),1));  
	    			SET @counter = @counter + 1;
	    		END WHILE;
	    		
	    		SET @VALUE = CONCAT("'" , @VALUE , "'");
	    		
	    	ELSEIF @TYPE = 'float' OR @TYPE = 'decimal' THEN
	    		SET @VALUE = ROUND(RAND() , 2);
	    	ELSEIF @TYPE = 'datetime'  THEN

	    		SET @VALUE = 'default';
	    	ELSE 
	    		SET @VALUE = '';
	    	END IF;
	    	
	-- 判断这个数是否是最后一个
			IF @columnNum = @columnSum THEN
				SET @insertSql = CONCAT(@insertSql , @VALUE , ')');
			ELSE 
				SET @insertSql = CONCAT(@insertSql , @VALUE , ',');
			END IF;
			
			SET @columnNum = @columnNum + 1;
		END WHILE;
	-- 执行
	SELECT @insertSql;
		PREPARE stmt FROM @insertSql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		
		SET @currNum = @currNum + 1;
	
	END WHILE;
	
END $$
DELIMITER ;

使用:
fillTable //存储过程名字
addNum //添加数量
table_name //表名
CALL fillTable( addNum,table_name)