打印表的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT
	COLUMN_NAME 列名,
	COLUMN_TYPE 数据类型,
	DATA_TYPE 字段类型,
	IS_NULLABLE 是否为空,
	COLUMN_DEFAULT 默认值,
	COLUMN_COMMENT 备注
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    table_schema = '数据库名' 
AND table_name = '数据库的表名'

随机生成100W数据到Mysql中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
SET GLOBAL log_bin_trust_function_creators = ON;


-- 创建一个 user 表
CREATE TABLE user (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  first_names VARCHAR(20) NOT NULL,
  last_names VARCHAR(20) NOT NULL,
  gender ENUM('M', 'F') NOT NULL,
  age INT UNSIGNED NOT NULL,
  email VARCHAR(50) NOT NULL,
  phone_number VARCHAR(20) NOT NULL,
  address VARCHAR(100) NOT NULL
);

-- 创建一个存储过程，用于生成随机数据并插入到 user 表中
DELIMITER //
CREATE PROCEDURE generate_user_data(IN total INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE first_name VARCHAR(20);
  DECLARE last_name VARCHAR(20);
  DECLARE gender CHAR(1);
  DECLARE age INT UNSIGNED;
  DECLARE email VARCHAR(50);
  DECLARE phone_number VARCHAR(20);
  DECLARE address VARCHAR(100);
  
  WHILE i <= total DO
    -- 生成随机数据
    SET first_name = CONCAT('First', FLOOR(RAND() * 1000000));
    SET last_name = CONCAT('Last', FLOOR(RAND() * 1000000));
    SET gender = IF(MOD(FLOOR(RAND() * 100), 2) = 0, 'M', 'F');
    SET age = FLOOR(RAND() * 100);
    SET email = CONCAT(first_name, last_name, '@example.com');
    SET phone_number = CONCAT('+1', FLOOR(RAND() * 1000000000));
    SET address = CONCAT(FLOOR(RAND() * 1000), ' Main St');
    
    -- 插入数据到 user 表中
    INSERT INTO user (first_names, last_names, gender, age, email, phone_number, address)
    VALUES (first_name, last_name, gender, age, email, phone_number, address);
    
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

-- 调用存储过程，生成100万条数据
CALL generate_user_data(1000000);