目录
一、Oracle数据库创建测试表
1、创建表需求
生成一张Oracle大表，数据量自行定义，可以几千、几万或几百万或者更多也ok。
创建用户测试表的： 列名及列属性（id number，以10000开头；name varchar2(20)，age number.年龄18-60岁；identification varchar2(18)，全部为18位的随机数字，且开头不为0）。 根据要求写个sql语句，插入数据10000行。 注意：name的值一定要是常规的美国国家姓名，不要随机出现字母等.
2、创建测试表
CREATE TABLE test ( ID NUMBER primary key, name VARCHAR2(50), age NUMBER, identification VARCHAR2(18), constraint identif_unique unique(identification) );
3、创建姓名随机函数
CREATE OR REPLACE FUNCTION generate_random_name RETURN VARCHAR2 IS v_first_names VARCHAR2(10000) := 'James,John,Robert,Michael,William,David,Richard,Joseph,Charles,Thomas,Christopher,Daniel,Matthew,Anthony,Donald,Mark,Paul,Steven,Andrew,Kenneth,George,Joshua,Edward,Brian,Ronald,Timothy,Jason,Jeffrey,Frank,Scott,Eric,Stephen,Andrew,Gary,Patrick,Michael,Terry,Brandon,Jacob,Sean,Ryan,Nicholas,Gregory,Peter,Jonathan,Justin,Scott,Benjamin,Walter,Marry,Rebecca,Jennifer,Linda,Patricia,Amy,Elizabeth,Susan,Karen,Nancy,Margaret,Dorothy,Helen,Sandra,Donna,Carol,Ruth,Sharon,Michelle,Laura,Sarah,Kimberly,Deborah,Jessica,Melissa'; v_last_names VARCHAR2(10000) := 'Smith,Johnson,Williams,Brown,Jones,Garcia,Miller,Davis,Rodriguez,Martinez,Hernandez,Lopez,Gonzalez,Wilson,Anderson,Thomas,Taylor,Moore,Jackson,Thompson,White,Lee,Lopez,Harris,Clark,Lewis,Robinson,Walker,Hall,Young,Allen,Wright,King,Scott,Green,Baker,Hill,Carter,Mitchell, Turner,Adams,James,Powell,Nelson,Campbell,Parker,Cooper,Ross,Ortiz,Brooks,Jenkins,Hughes,Reyes,Morgan,Rogers,Peterson,Bell,Boyd,Bailey,Reed,James,Coleman,Gray,Ramirez,Gray,Ramirez,Kelly,Howard,Ward,Cox,Diaz,Richardson,Wood,Watson,Brooks,Bennett,Gray,James,Reed,Foster,Gonzalez'; v_first_name VARCHAR2(50); v_last_name VARCHAR2(50); BEGIN v_first_name := REGEXP_SUBSTR(v_first_names, '[^,]+', 1, ROUND(DBMS_RANDOM.VALUE(1, REGEXP_COUNT(v_first_names, ',') + 2))); v_last_name := REGEXP_SUBSTR(v_last_names, '[^,]+', 1, ROUND(DBMS_RANDOM.VALUE(1, REGEXP_COUNT(v_last_names, ',') + 2))); RETURN v_first_name || ' ' || v_last_name; END; /
4、生成测试数据
DECLARE i NUMBER := 1; random_age NUMBER; random_identification VARCHAR2(18); COUNT_ID number; BEGIN FOR i IN 1..1000000 LOOP --循环插入100万条数据。 random_age := ROUND(DBMS_RANDOM.VALUE(0, 120)); --生成一个随机的年龄值（0到120之间），用于测试 LOOP -- 生成一个随机的18位身份证号码，用于测试 random_identification := LPAD(TRUNC(DBMS_RANDOM.VALUE(1, 999999999999999999)), 18, '0'); SELECT COUNT(*) INTO COUNT_ID FROM test WHERE identification = random_identification; -- 检查生成的身份证号码是否已存在于表中 EXIT WHEN COUNT_ID = 0 AND SUBSTR(random_identification, 1, 1) <> '0'; END LOOP; INSERT INTO test (ID, name, age, identification) VALUES (10000 + i, generate_random_name(), random_age, random_identification); IF MOD(i, 1000) = 0 THEN --每1000条数据提交一次事务 COMMIT; END IF; END LOOP; COMMIT; END; /
二、PostgreSQL数据库创建测试表
1、要求
postgresql下创建用户测试表的： 列名及列属性（id integer，以10000开头；name,varchar，age integer 年龄18-60岁；identification varchar，全部为18位的随机数字，且开头不为0）。 根据要求写个sql语句，插入数据10000行。 注意：name的值一定要是常规的美国国家姓名，不要随机出现字母等.
2、创建用户测试表
CREATE TABLE user_test ( id INTEGER primary key, name varchar, age INTEGER, identification VARCHAR(18), constraint id_unique unique(identification) );
3、创建函数，用于生成指定长度的随机数字字符串
CREATE OR REPLACE FUNCTION generate_random_number(length INTEGER) RETURNS VARCHAR AS $$ DECLARE result VARCHAR; BEGIN result := ''; FOR i IN 1..length LOOP result := result || floor(random() * 9 + 1)::text; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql;
4、创建函数，用于生成随机姓名（姓氏与名字组合大约有100-200万不重复）
CREATE OR REPLACE FUNCTION generate_usa_name() RETURNS VARCHAR LANGUAGE plpgsql AS $$ DECLARE first_names TEXT[] := ARRAY[ 'Smith','Johnson','Williams','Jones','Brown','Davis','Miller','Wilson','Moore','Taylor','Anderson','Thomas', 'Jackson','White','Harris','Martin','Thompson','Garcia','Martinez','Robinson','Clark','Rodriguez','Lewis','Lee', 'Walker','Hall','Allen','Young','Hernandez','King','Wright','Lopez','Scott','Green','Adams','Baker','Gonzalez', 'Nelson','Carter','Mitchell','Perez','Roberts','Turner','Phillips','Campbell','Parker','Evans','Edwards','Collins', 'Stewart','Sanchez','Morris','Rogers','Reed','Cook','Morgan','Bell','Murphy','Bailey','Cooper','Richardson','Cox', 'Howard','Ward','Torres','Peterson','Gray','Ramirez','James','Watson','Brooks','Kelly','Sanders','Price','Bennett', 'Wood','Barnes','Ross','Henderson','Coleman','Jenkins','Perry','Powell','Long','Patterson','Hughes','Flores','Washington', 'Butler','Simmons','Foster','Gonzales','Bryant','Alexander','Russell','Griffin','Diaz','Hayes','Myers','Ford','Hamilton','Graham', 'Sullivan','Wallace','Woods','Cole','West','Jordan','Owens','Reynolds','Fisher','Ellis','Harrison','Gibson','Mcdonald','Cruz','Marshall','Ortiz', 'Gomez','Murray','Freeman','Wells','Webb','Simpson','Stevens','Tucker','Porter','Hunter','Hicks','Crawford','Henry','Boyd','Mason','Morales','Kennedy', 'Warren','Dixon','Ramos','Reyes','Burns','Gordon','Shaw','Holmes','Rice','Robertson','Hunt','Black','Daniels','Palmer','Mills','Nichols','Grant','Knight', 'Ferguson','Rose','Stone','Hawkins','Dunn','Perkins','Hudson','Spencer','Gardner','Stephens','Payne','Pierce','Berry','Matthews','Arnold','Wagner','Willis', 'Ray','Watkins','Olson','Carroll','Duncan','Snyder','Hart','Cunningham','Bradley','Lane','Andrews','Ruiz','Harper','Fox','Riley','Armstrong','Carpenter', 'Weaver','Greene','Lawrence','Elliott','Chavez','Sims','Austin','Peters','Kelley','Franklin','Lawson','Fields','Gutierrez','Ryan','Schmidt','Carr','Vasquez', 'Castillo','Wheeler','Chapman','Oliver','Montgomery','Richards','Williamson','Johnston','Banks','Meyer','Bishop','Mccoy','Howell','Alvarez','Morrison', 'Hansen','Fernandez','Garza','Harvey','Little','Burton','Stanley','Nguyen','George','Jacobs','Reid','Kim','Fuller','Lynch','Dean','Gilbert','Garrett', 'Romero','Welch','Larson','Frazier','Burke','Hanson','Day','Mendoza','Moreno','Bowman','Medina','Fowler','Brewer','Hoffman','Carlson','Silva','Pearson', 'Holland','Douglas','Fleming','Jensen','Vargas','Byrd','Davidson','Hopkins','May','Terry','Herrera','Wade','Soto','Walters','Curtis','Neal','Caldwell', 'Lowe','Jennings','Barnett','Graves','Jimenez','Horton','Shelton','Barrett','Obrien','Castro','Sutton','Gregory','Mckinney','Lucas','Miles','Craig', 'Rodriquez','Chambers','Holt','Lambert','Fletcher','Watts','Bates','Hale','Rhodes','Pena','Beck','Newman','Haynes','Mcdaniel','Mendez','Bush','Vaughn', 'Parks','Dawson','Santiago','Norris','Hardy','Love','Steele','Curry','Powers','Schultz','Barker','Guzman','Page','Munoz','Ball','Keller','Chandler', 'Weber','Leonard','Walsh','Lyons','Ramsey','Wolfe','Schneider','Mullins','Benson','Sharp','Bowen','Daniel','Barber','Cummings','Hines','Baldwin', 'Griffith','Valdez','Hubbard','Salazar','Reeves','Warner','Stevenson','Burgess','Santos','Tate','Cross','Garner','Mann','Mack','Moss','Thornton', 'Dennis','Mcgee','Farmer','Delgado','Aguilar','Vega','Glover','Manning','Cohen','Harmon','Rodgers','Robbins','Newton','Todd','Blair','Higgins', 'Ingram','Reese','Cannon','Strickland','Townsend','Potter','Goodwin','Walton','Rowe','Hampton','Ortega','Patton','Swanson','Joseph','Francis', 'Goodman','Maldonado','Yates','Becker','Erickson','Hodges','Rios','Conner','Adkins','Webster','Norman','Malone','Hammond','Flowers','Cobb','Moody', 'Quinn','Blake','Maxwell','Pope','Floyd','Osborne','Paul','Mccarthy','Guerrero','Lindsey','Estrada','Sandoval','Gibbs','Tyler','Gross','Fitzgerald', 'Stokes','Doyle','Sherman','Saunders','Wise','Colon','Gill','Alvarado','Hodge','Acosta','Estes','Connolly','Gallagher','Roberts','Todd','Jones','Williams' ]; last_names TEXT[] := ARRAY[ 'Smith','Johnson','Williams','Jones','Brown','Davis','Miller','Wilson','Moore','Taylor','Anderson','Thomas','Jackson','White','Harris','Martin','Thompson', 'Garcia','Martinez','Robinson','Clark','Rodriguez','Lewis','Lee','Walker','Hall','Allen','Young','Hernandez','King','Wright','Lopez','Scott','Green','Adams', 'Baker','Gonzalez','Nelson','Carter','Mitchell','Perez','Roberts','Turner','Phillips','Campbell','Parker','Evans','Edwards','Collins','Stewart','Sanchez', 'Morris','Rogers','Reed','Cook','Morgan','Bell','Murphy','Bailey','Cooper','Richardson','Cox','Howard','Ward','Torres','Peterson','Gray','Ramirez','James', 'Watson','Brooks','Kelly','Sanders','Price','Bennett','Wood','Barnes','Ross','Henderson','Coleman','Jenkins','Perry','Powell','Long','Patterson','Hughes', 'Flores','Washington','Butler','Simmons','Foster','Gonzales','Bryant','Alexander','Russell','Griffin','Diaz','Hayes','Mye','Ford','Hamilton','Graham','Sullivan', 'Wallace','Woods','Cole','West','Jordan','Owens','Reynolds','Fisher','Ellis','Harrison','Gibson','McDonald','Cruz','Marshall','Ortiz','Gomez','Murray','Freeman', 'Wells','Webb','Simpson','Stevens','Tucker','Porter','Hunter','Hicks','Crawford','Henry','Boyd','Mason','Morales','Kennedy','Warren','Dixon','Ramos','Reyes', 'Burns','Gordon','Shaw','Holmes','Rice','Robertson','Hunt','Black','Daniels','Palmer','Mills','Nichols','Grant','Knight','Ferguson','Rose','Stone','Hawkins','Dunn', 'Perkins','Hudson','Spencer','Gardner','Stephens','Payne','Pierce','Berry','Matthews','Arnold','Wagner','Willis','Ray','Watkins','Olson','Carroll','Duncan', 'Snyder','Hart','Cunningham','Bradley','Lane','Andrews','Ruiz','Harper','Fox','Riley','Armstrong','Carpenter','Weber','Greene','Lawrence','Elliott','Chavez', 'Sims','Austin','Peters','Kelley','Franklin','Lawson','Fields','Gutierrez','Ryan','Schmidt','Carr','Vasquez','Castillo','Wheeler','Chapman','Oliver','Montgomery', 'Richards','Williamson','Johnston','Bryant','Hudson','Roberts','Becker','Holland','Banks','Warner','Fisher','Gibson','Fox','Nelson','Daniels','Stewart','West', 'Cunningham','Baker','Collins','Reed','Phillips','Perez','Lopez','Bennett','Butler','Coleman','Mitchell','Price','Woods','Foster','Sullivan','Harrison','Kelly', 'Kennedy','Wallace','Perry','Howard','Rice','Jenkins','Peters','Andrews','Ortiz','Gomez','Gonzales','Lawrence','Garza','Sims','Murray','Jordan','Reyes','Snyder', 'Flynn','Dixon','Walters','Whitehead','Best','Wade','Blackburn','Putnam','Franklin','Malone','Chaney','Pennington','Hilliard','Wilkerson','Abrams','Terrell', 'Osborn','Guy','Wooten','Navarro','Hicks','Manning','Knox','Gibbs','Sanchez','Carrillo','Noble','Harrington','Dominguez','Burgess','Santos','Tate','Cross','Garner', 'Mann','Moss','Thornton','Dennis','McGuire','Orr','Hale','Rhodes','Pena','Beck','Newman','Haynes','Caldwell','Lambert','Guerrero','Sanders','Hubbard','Hansen', 'Reeves','Blake','Cisneros','Benitez','Tyler','Vaughn','Winkler','Wilcox','Gill','Fuentes','Sawyer','Espinoza','Nicholson','Monroe','Wolf','Moran','Colon','Salinas', 'Yates','Campos','Contreras','Wilkins','Dickerson','Hess','Li','Beard','Carver','Rosales','Delgado','Hancock','Cabrera','Vincent','Hoover','Faulkner','Jimenez', 'Lara','Byrd','Scott','Cuevas','House','Thorpe','Cassidy','Blevins','McGee','Hoffman','Hull','Pittman','Brady','Madden','Conner','Barton','Klein','Davidson','Cantu', 'Himes','Sexton','Levy','Hardy','Gates','Camacho','Baines','Vargas','Clarke','Gould','Huynh','Lucas','Buchanan','Ramos','Glover','Hale','Garza','Mooney', 'Church','Harrington','Hartman','Maynard','Walls','Palmer','Roberts','Frost','Terrell','Giles','Khan','Yoder','Baca','Miller','Perry','Fletcher', 'Phelps','Clements','George','Rhodes','Haynes','Crosby','Douglas','Soto','Aguilar','Henry','Freeman','Duran','Simmons','Owens','Reeves','Newton', 'Abbott','Malone','Lynn','Morse','Mann','Leblanc','Hodge','Farrell','Hawkins','Lowe','Clayton','Heath','Bean','Boyer','Pittman','Read','Waller', 'Rush','Mcintosh','Coffey','England','Covington','Gallagher','Romero','Mosley','Baird','Bowman','Parsons','Sloan','Cuevas','Shields','Skinner', 'Poole','Norton','Bolton','Evans','Alvarez','Greer','Shaw','Chan','Fry','Daugherty','Chavez','Ali','Himes','Trevino','Hopper','Byers','Ware','Guzman','Hatfield','Morrow', 'Gilmore','Rollins','Leone','Huffman','Bender','Conway','Austin','Montes','Moon','Browning','Melton','Krueger', 'Pruitt','Larson','Singleton','Woods','Mckay','Maddox', 'Talley','Valentine','Baker','Cummings','Wilkins','Floyd','Frye','Combs','Hudson','Phipps', 'Pacheco','Solomon','Torres','Huff','Hogan','Abbott','Sheppard','Bonner','Wright','Hodge','Barrera','Moore','Blake','Bruce','Brooks','Nicholas', 'Bullock','Hobbs','Gaines','Wilkinson','Sherman','Wiggins','Graves','Rosales','Walls','Dickson','Donovan','Crockett','Dennis','Perkins','Potts','Ho', 'Redmond','Shah','Heath','Hines','Gonzalez','Horne','Benjamin','Earl','Hart','Bowen','Dickens','Thornton','Hawkins','Wilcox','Branch','Maxwell', 'Mcgee','Richmond','Dominguez','Kline','Shah','Wyatt','Allison','Reed','Haynes','Sandoval','Wright','Carpenter','Gallegos','Kirby','June','Davies', 'Howell','Cobb','Harris','Wagner','Pineda','Haney','Burns','Owens','Sweeney','Carey','Summers','Joyner','Heath','Stout','Patel','Berry','Lawrence', 'Webster','Newman','Montgomery','Choi','Wolfe','Dawson','Briggs','Kramer','Paul','Stephenson','Fritz','Villarreal','Woods','Lucero','Simmons','Hess', 'Rasmussen','Koch','Moyer','Shea','Lin','Gordon','Werner','Bush' ]; title_prefixes TEXT[] := ARRAY[ 'Mr.', 'Mrs.', 'Mss.', 'Dr.', 'Prof.' ]; first_name TEXT; last_name TEXT; prefix TEXT; BEGIN first_name := first_names[(floor(random() * array_length(first_names, 1) + 1))::integer]; last_name := last_names[(floor(random() * array_length(last_names, 1) + 1))::integer]; prefix := title_prefixes[(floor(random() * array_length(title_prefixes, 1) + 1))::integer]; RETURN first_name || ' ' || last_name; END; $$;
5、插入数据
INSERT INTO user_test (id,name,age, identification) SELECT (10000 + n) AS id, generate_usa_name() as name, floor(random() * (60 - 18 + 1) + 18) AS age, generate_random_number(18) AS identification FROM generate_series(1, 10000) AS n;
三、MySQL数据库创建测试表
1、要求
MySQL下创建用户测试表的： 列名及列属性（id int，以10000开头；name为varchar，age为int年龄18-60岁；identification varchar，全部为18位的随机数字，且开头不为0）。 根据要求写个sql语句，插入数据10000行。 注意：name的值一定要是常规的美国国家姓名，不要随机出现字母等.
2、创建用户测试表
CREATE TABLE user_test (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age TINYINT,
identification VARCHAR(18)
);
-- 设置 id 从 10000 开始自增
ALTER TABLE user_test AUTO_INCREMENT = 10000;
drop PROCEDURE insert_user_test_data;
3、创建临时表，用于存放姓和名
-- 创建临时表存储名字
CREATE TEMPORARY TABLE temp_first_names ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) );
-- 插入名字
INSERT INTO temp_first_names (name)
VALUES ('Smith'),('Johnson'),('Williams'),('Jones'),('Brown'),('Davis'),('Miller'),('Wilson'),('Moore'),('Taylor'),('Anderson'),('Thomas'),('Jackson'),('White'),('Harris'),('Martin'),('Thompson'),('Garcia'),('Martinez'),('Robinson'),('Clark'),('Rodriguez'),('Lewis'),('Lee'),('Walker'),('Hall'),('Allen'),('Young'),('Hernandez'),('King'),('Wright'),('Lopez'),('Scott'),('Green'),('Adams'),('Baker'),('Gonzalez'),('Nelson'),('Carter'),('Mitchell'),('Perez'),('Roberts'),('Turner'),('Phillips'),('Campbell'),('Parker'),('Evans'),('Edwards'),('Collins'),('Stewart'),('Sanchez'),('Morris'),('Rogers'),('Reed'),('Cook'),('Morgan'),('Bell'),('Murphy'),('Bailey'),('Cooper'),('Richardson'),('Cox'),('Howard'),('Ward'),('Torres'),('Peterson'),('Gray'),('Ramirez'),('James'),('Watson'),('Brooks'),('Kelly'),('Sanders'),('Price'),('Bennett'),('Wood'),('Barnes'),('Ross'),('Henderson'),('Coleman'),('Jenkins'),('Perry'),('Powell'),('Long'),('Patterson'),('Hughes'),('Flores'),('Washington'),('Butler'),('Simmons'),('Foster'),('Gonzales'),('Bryant'),('Alexander'),('Russell'),('Griffin'),('Diaz'),('Hayes'),('Myers'),('Ford'),('Hamilton'),('Graham'),('Sullivan'),('Wallace'),('Woods'),('Cole'),('West'),('Jordan'),('Owens'),('Reynolds'),('Fisher'),('Ellis'),('Harrison'),('Gibson'),('Mcdonald'),('Cruz'),('Marshall'),('Ortiz'),('Gomez'),('Murray'),('Freeman'),('Wells'),('Webb'),('Simpson'),('Stevens'),('Tucker'),('Porter'),('Hunter'),('Hicks'),('Crawford'),('Henry'),('Boyd'),('Mason'),('Morales'),('Kennedy'),('Warren'),('Dixon'),('Ramos'),('Reyes'),('Burns'),('Gordon'),('Shaw'),('Holmes'),('Rice'),('Robertson'),('Hunt'),('Black'),('Daniels'),('Palmer'),('Mills'),('Nichols'),('Grant'),('Knight'),('Ferguson'),('Rose'),('Stone'),('Hawkins'),('Dunn'),('Perkins'),('Hudson'),('Spencer'),('Gardner'),('Stephens'),('Payne'),('Pierce'),('Berry'),('Matthews'),('Arnold'),('Wagner'),('Willis'),('Ray'),('Watkins'),('Olson'),('Carroll'),('Duncan'),('Snyder'),('Hart'),('Cunningham'),('Bradley'),('Lane'),('Andrews'),('Ruiz'),('Harper'),('Fox'),('Riley'),('Armstrong'),('Carpenter'),('Weaver'),('Greene'),('Lawrence'),('Elliott'),('Chavez'),('Sims'),('Austin'),('Peters'),('Kelley'),('Franklin'),('Lawson'),('Fields'),('Gutierrez'),('Ryan'),('Schmidt'),('Carr'),('Vasquez'),('Castillo'),('Wheeler'),('Chapman'),('Oliver'),('Montgomery'),('Richards'),('Williamson'),('Johnston'),('Banks'),('Meyer'),('Bishop'),('Mccoy'),('Howell'),('Alvarez'),('Morrison'),('Hansen'),('Fernandez'),('Garza'),('Harvey'),('Little'),('Burton'),('Stanley'),('Nguyen'),('George'),('Jacobs'),('Reid'),('Kim'),('Fuller'),('Lynch'),('Dean'),('Gilbert'),('Garrett'),('Romero'),('Welch'),('Larson'),('Frazier'),('Burke'),('Hanson'),('Day'),('Mendoza'),('Moreno'),('Bowman'),('Medina'),('Fowler'),('Brewer'),('Hoffman'),('Carlson'),('Silva'),('Pearson'),('Holland'),('Douglas'),('Fleming'),('Jensen'),('Vargas'),('Byrd'),('Davidson'),('Hopkins'),('May'),('Terry'),('Herrera'),('Wade'),('Soto'),('Walters'),('Curtis'),('Neal'),('Caldwell'),('Lowe'),('Jennings'),('Barnett'),('Graves'),('Jimenez'),('Horton'),('Shelton'),('Barrett'),('Obrien'),('Castro'),('Sutton'),('Gregory'),('Mckinney'),('Lucas'),('Miles'),('Craig'),('Rodriquez'),('Chambers'),('Holt'),('Lambert'),('Fletcher'),('Watts'),('Bates'),('Hale'),('Rhodes'),('Pena'),('Beck'),('Newman'),('Haynes'),('Mcdaniel'),('Mendez'),('Bush'),('Vaughn'),('Parks'),('Dawson'),('Santiago'),('Norris'),('Hardy'),('Love'),('Steele'),('Curry'),('Powers'),('Schultz'),('Barker'),('Guzman'),('Page'),('Munoz'),('Ball'),('Keller'),('Chandler'),('Weber'),('Leonard'),('Walsh'),('Lyons'),('Ramsey'),('Wolfe'),('Schneider'),('Mullins'),('Benson'),('Sharp'),('Bowen'),('Daniel'),('Barber'),('Cummings'),('Hines'),('Baldwin'),('Griffith'),('Valdez'),('Hubbard'),('Salazar'),('Reeves'),('Warner'),('Stevenson'),('Burgess'),('Santos'),('Tate'),('Cross'),('Garner'),('Mann'),('Mack'),('Moss'),('Thornton'),('Dennis'),('Mcgee'),('Farmer'),('Delgado'),('Aguilar'),('Vega'),('Glover'),('Manning'),('Cohen'),('Harmon'),('Rodgers'),('Robbins'),('Newton'),('Todd'),('Blair'),('Higgins'),('Ingram'),('Reese'),('Cannon'),('Strickland'),('Townsend'),('Potter'),('Goodwin'),('Walton'),('Rowe'),('Hampton'),('Ortega'),('Patton'),('Swanson'),('Joseph'),('Francis'),('Goodman'),('Maldonado'),('Yates'),('Becker'),('Erickson'),('Hodges'),('Rios'),('Conner'),('Adkins'),('Webster'),('Norman'),('Malone'),('Hammond'),('Flowers'),('Cobb'),('Moody'),('Quinn'),('Blake'),('Maxwell'),('Pope'),('Floyd'),('Osborne'),('Paul'),('Mccarthy'),('Guerrero'),('Lindsey'),('Estrada'),('Sandoval'),('Gibbs'),('Tyler'),('Gross'),('Fitzgerald'),('Stokes'),('Doyle'),('Sherman'),('Saunders'),('Wise'),('Colon'),('Gill'),('Alvarado'),('Hodge'),('Acosta'),('Estes'),('Connolly'),('Gallagher'),('Roberts'),('Todd'),('Jones'),('Williams')
-- 创建临时表存储姓氏
CREATE TEMPORARY TABLE temp_last_names ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) );
-- 插入姓氏
INSERT INTO temp_last_names (name)
VALUES ('Smith'),('Johnson'),('Williams'),('Jones'),('Brown'),('Davis'),('Miller'),('Wilson'),('Moore'),('Taylor'),('Anderson'),('Thomas'),('Jackson'),('White'),('Harris'),('Martin'),('Thompson'),('Garcia'),('Martinez'),('Robinson'),('Clark'),('Rodriguez'),('Lewis'),('Lee'),('Walker'),('Hall'),('Allen'),('Young'),('Hernandez'),('King'),('Wright'),('Lopez'),('Scott'),('Green'),('Adams'),('Baker'),('Gonzalez'),('Nelson'),('Carter'),('Mitchell'),('Perez'),('Roberts'),('Turner'),('Phillips'),('Campbell'),('Parker'),('Evans'),('Edwards'),('Collins'),('Stewart'),('Sanchez'),('Morris'),('Rogers'),('Reed'),('Cook'),('Morgan'),('Bell'),('Murphy'),('Bailey'),('Cooper'),('Richardson'),('Cox'),('Howard'),('Ward'),('Torres'),('Peterson'),('Gray'),('Ramirez'),('James'),('Watson'),('Brooks'),('Kelly'),('Sanders'),('Price'),('Bennett'),('Wood'),('Barnes'),('Ross'),('Henderson'),('Coleman'),('Jenkins'),('Perry'),('Powell'),('Long'),('Patterson'),('Hughes'),('Flores'),('Washington'),('Butler'),('Simmons'),('Foster'),('Gonzales'),('Bryant'),('Alexander'),('Russell'),('Griffin'),('Diaz'),('Hayes'),('Mye'),('Ford'),('Hamilton'),('Graham'),('Sullivan'),('Wallace'),('Woods'),('Cole'),('West'),('Jordan'),('Owens'),('Reynolds'),('Fisher'),('Ellis'),('Harrison'),('Gibson'),('McDonald'),('Cruz'),('Marshall'),('Ortiz'),('Gomez'),('Murray'),('Freeman'),('Wells'),('Webb'),('Simpson'),('Stevens'),('Tucker'),('Porter'),('Hunter'),('Hicks'),('Crawford'),('Henry'),('Boyd'),('Mason'),('Morales'),('Kennedy'),('Warren'),('Dixon'),('Ramos'),('Reyes'),('Burns'),('Gordon'),('Shaw'),('Holmes'),('Rice'),('Robertson'),('Hunt'),('Black'),('Daniels'),('Palmer'),('Mills'),('Nichols'),('Grant'),('Knight'),('Ferguson'),('Rose'),('Stone'),('Hawkins'),('Dunn'),('Perkins'),('Hudson'),('Spencer'),('Gardner'),('Stephens'),('Payne'),('Pierce'),('Berry'),('Matthews'),('Arnold'),('Wagner'),('Willis'),('Ray'),('Watkins'),('Olson'),('Carroll'),('Duncan'),('Snyder'),('Hart'),('Cunningham'),('Bradley'),('Lane'),('Andrews'),('Ruiz'),('Harper'),('Fox'),('Riley'),('Armstrong'),('Carpenter'),('Weber'),('Greene'),('Lawrence'),('Elliott'),('Chavez'),('Sims'),('Austin'),('Peters'),('Kelley'),('Franklin'),('Lawson'),('Fields'),('Gutierrez'),('Ryan'),('Schmidt'),('Carr'),('Vasquez'),('Castillo'),('Wheeler'),('Chapman'),('Oliver'),('Montgomery'),('Richards'),('Williamson'),('Johnston'),('Bryant'),('Hudson'),('Roberts'),('Becker'),('Holland'),('Banks'),('Warner'),('Fisher'),('Gibson'),('Fox'),('Nelson'),('Daniels'),('Stewart'),('West'),('Cunningham'),('Baker'),('Collins'),('Reed'),('Phillips'),('Perez'),('Lopez'),('Bennett'),('Butler'),('Coleman'),('Mitchell'),('Price'),('Woods'),('Foster'),('Sullivan'),('Harrison'),('Kelly'),('Kennedy'),('Wallace'),('Perry'),('Howard'),('Rice'),('Jenkins'),('Peters'),('Andrews'),('Ortiz'),('Gomez'),('Gonzales'),('Lawrence'),('Garza'),('Sims'),('Murray'),('Jordan'),('Reyes'),('Snyder'),('Flynn'),('Dixon'),('Walters'),('Whitehead'),('Best'),('Wade'),('Blackburn'),('Putnam'),('Franklin'),('Malone'),('Chaney'),('Pennington'),('Hilliard'),('Wilkerson'),('Abrams'),('Terrell'),('Osborn'),('Guy'),('Wooten'),('Navarro'),('Hicks'),('Manning'),('Knox'),('Gibbs'),('Sanchez'),('Carrillo'),('Noble'),('Harrington'),('Dominguez'),('Burgess'),('Santos'),('Tate'),('Cross'),('Garner'),('Mann'),('Moss'),('Thornton'),('Dennis'),('McGuire'),('Orr'),('Hale'),('Rhodes'),('Pena'),('Beck'),('Newman'),('Haynes'),('Caldwell'),('Lambert'),('Guerrero'),('Sanders'),('Hubbard'),('Hansen'),('Reeves'),('Blake'),('Cisneros'),('Benitez'),('Tyler'),('Vaughn'),('Winkler'),('Wilcox'),('Gill'),('Fuentes'),('Sawyer'),('Espinoza'),('Nicholson'),('Monroe'),('Wolf'),('Moran'),('Colon'),('Salinas'),('Yates'),('Campos'),('Contreras'),('Wilkins'),('Dickerson'),('Hess'),('Li'),('Beard'),('Carver'),('Rosales'),('Delgado'),('Hancock'),('Cabrera'),('Vincent'),('Hoover'),('Faulkner'),('Jimenez'),('Lara'),('Byrd'),('Scott'),('Cuevas'),('House'),('Thorpe'),('Cassidy'),('Blevins'),('McGee'),('Hoffman'),('Hull'),('Pittman'),('Brady'),('Madden'),('Conner'),('Barton'),('Klein'),('Davidson'),('Cantu'),('Himes'),('Sexton'),('Levy'),('Hardy'),('Gates'),('Camacho'),('Baines'),('Vargas'),('Clarke'),('Gould'),('Huynh'),('Lucas'),('Buchanan'),('Ramos'),('Glover'),('Hale'),('Garza'),('Mooney'),('Church'),('Harrington'),('Hartman'),('Maynard'),('Walls'),('Palmer'),('Roberts'),('Frost'),('Terrell'),('Giles'),('Khan'),('Yoder'),('Baca'),('Miller'),('Perry'),('Fletcher'),('Phelps'),('Clements'),('George'),('Rhodes'),('Haynes'),('Crosby'),('Douglas'),('Soto'),('Aguilar'),('Henry'),('Freeman'),('Duran'),('Simmons'),('Owens'),('Reeves'),('Newton'),('Abbott'),('Malone'),('Lynn'),('Morse'),('Mann'),('Leblanc'),('Hodge'),('Farrell'),('Hawkins'),('Lowe'),('Clayton'),('Heath'),('Bean'),('Boyer'),('Pittman'),('Read'),('Waller'),('Rush'),('Mcintosh'),('Coffey'),('England'),('Covington'),('Gallagher'),('Romero'),('Mosley'),('Baird'),('Bowman'),('Parsons'),('Sloan'),('Cuevas'),('Shields'),('Skinner'),('Poole'),('Norton'),('Bolton'),('Evans'),('Alvarez'),('Greer'),('Shaw'),('Chan'),('Fry'),('Daugherty'),('Chavez'),('Ali'),('Himes'),('Trevino'),('Hopper'),('Byers'),('Ware'),('Guzman'),('Hatfield'),('Morrow'),('Gilmore'),('Rollins'),('Leone'),('Huffman'),('Bender'),('Conway'),('Austin'),('Montes'),('Moon'),('Browning'),('Melton'),('Krueger'),('Pruitt'),('Larson'),('Singleton'),('Woods'),('Mckay'),('Maddox'),('Talley'),('Valentine'),('Baker'),('Cummings'),('Wilkins'),('Floyd'),('Frye'),('Combs'),('Hudson'),('Phipps'),('Pacheco'),('Solomon'),('Torres'),('Huff'),('Hogan'),('Abbott'),('Sheppard'),('Bonner'),('Wright'),('Hodge'),('Barrera'),('Moore'),('Blake'),('Bruce'),('Brooks'),('Nicholas'),('Bullock'),('Hobbs'),('Gaines'),('Wilkinson'),('Sherman'),('Wiggins'),('Graves'),('Rosales'),('Walls'),('Dickson'),('Donovan'),('Crockett'),('Dennis'),('Perkins'),('Potts'),('Ho'),('Redmond'),('Shah'),('Heath'),('Hines'),('Gonzalez'),('Horne'),('Benjamin'),('Earl'),('Hart'),('Bowen'),('Dickens'),('Thornton'),('Hawkins'),('Wilcox'),('Branch'),('Maxwell'),('Mcgee'),('Richmond'),('Dominguez'),('Kline'),('Shah'),('Wyatt'),('Allison'),('Reed'),('Haynes'),('Sandoval'),('Wright'),('Carpenter'),('Gallegos'),('Kirby'),('June'),('Davies'),('Howell'),('Cobb'),('Harris'),('Wagner'),('Pineda'),('Haney'),('Burns'),('Owens'),('Sweeney'),('Carey'),('Summers'),('Joyner'),('Heath'),('Stout'),('Patel'),('Berry'),('Lawrence'),('Webster'),('Newman'),('Montgomery'),('Choi'),('Wolfe'),('Dawson'),('Briggs'),('Kramer'),('Paul'),('Stephenson'),('Fritz'),('Villarreal'),('Woods'),('Lucero'),('Simmons'),('Hess'),('Rasmussen'),('Koch'),('Moyer'),('Shea'),('Lin'),('Gordon'),('Werner'),('Bush');
4、创建函数，用于生成随机姓名（姓氏与名字组合大约有100-200万不重复）
DELIMITER // CREATE PROCEDURE insert_user_test_data() BEGIN DECLARE i INT DEFAULT 1; DECLARE f_name VARCHAR(50); DECLARE l_name VARCHAR(50); DECLARE user_name VARCHAR(100); DECLARE user_age INT; DECLARE user_identification VARCHAR(18); WHILE i <= 10000 DO -- 从临时表中随机选择名字和姓氏 SELECT name INTO f_name FROM temp_first_names ORDER BY RAND() LIMIT 1; SELECT name INTO l_name FROM temp_last_names ORDER BY RAND() LIMIT 1; SET user_name = CONCAT(f_name, ' ', l_name); -- 随机生成年龄 SET user_age = FLOOR(18 + (RAND() * 43)); -- 随机生成18位身份证号码，且开头不为0 SET user_identification = CONCAT(FLOOR(1 + (RAND() * 9)), LPAD(FLOOR(RAND() * 100000000000000000), 17, '0')); SET user_identification = CONCAT(FLOOR(1 + (RAND() * 9)), LPAD(FLOOR(RAND() * 100000000000000000), 17, '0')); -- 插入数据 INSERT INTO user_test (name, age, identification) VALUES (user_name, user_age, user_identification); SET i = i + 1; END WHILE; END // DELIMITER ;
5、插入数据
CALL insert_user_test_data();
