sql语句创建1万条数据

– 测试

CREATE TABLE IF NOT EXISTSapp_user(
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR (50) DEFAULT ‘’ COMMENT ‘用户名昵称’,
email VARCHAR (50) NOT NULL COMMENT ‘邮箱’,
phone VARCHAR (2) DEFAULT ‘’ COMMENT ‘手机号’,
gender TINYINT(4) UNSIGNED DEFAULT ‘0’ COMMENT ‘性别(0：男; 1:女)’,
password VARCHAR (100) NOT NULL COMMENT’密码’,
age TINYINT(4) DEFAULT ‘0’ COMMENT ‘年龄’,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT=‘APP用户表’

– 插入一百万条数据

/*
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，让存储过程内的命令遇到”;”不执行\ast /DELIMITER$$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO app_user(name,email,phone,gender)
VALUES(CONCAT(‘用户’,i),‘19224305@qq.com’,‘123456789’,FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;

SELECT mock_data(); – 执行此函数 生成一百万条数据

– 查询

SELECT * FROM app_user WHERE name = ‘用户9999’; – 执行耗时 : 0.490 sec

EXPLAIN SELECT * FROM app_user WHERE name = ‘用户9999’;

– 索引 查询
– id _ 表名 _ 字段名
– CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(name);

SELECT * FROM app_user WHERE name = ‘用户9999’;

– 执行耗时 : 0 sec