MySQL数据库衍生出很多兼容他的数据库产品，Mariadb、OceanBase (开源mysql兼容版本)、PolarDB for MySQL 等这些数据库产品都兼容MySQL.国产的项目不允许有MySQL的存在，导致大部分乙方的产品都在研究信创数据库，dump 完Oracle，继续dump MySQL.
CREATE DATABASE IF NOT EXISTS test; USE test; CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50), email VARCHAR(100), signup_date DATE ); CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), price FLOAT, created_at DATE ); CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, product_id INT, order_date DATE, amount FLOAT, note TEXT, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (product_id) REFERENCES products(product_id) );
DELIMITER $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) DETERMINISTIC BEGIN DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE result VARCHAR(255) DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i < n DO SET result = CONCAT(result, SUBSTRING(chars, FLOOR(1 + RAND() * 62), 1)); SET i = i + 1; END WHILE; RETURN result; END$$ DELIMITER ;
DELIMITER $$ CREATE PROCEDURE insert_users(IN total INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < total DO INSERT INTO users (username, email, signup_date) VALUES ( rand_string(8), CONCAT(rand_string(5), '@example.com'), DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365 * 5) DAY) ); SET i = i + 1; END WHILE; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE insert_products(IN total INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < total DO INSERT INTO products (name, price, created_at) VALUES ( rand_string(10), ROUND(RAND() * 5000, 2), DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365 * 5) DAY) ); SET i = i + 1; END WHILE; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE insert_orders(IN total INT) BEGIN DECLARE i INT DEFAULT 0; DECLARE uid INT; DECLARE pid INT; DECLARE user_count INT; DECLARE product_count INT; SELECT COUNT(*) INTO user_count FROM users; SELECT COUNT(*) INTO product_count FROM products; WHILE i < total DO SET uid = FLOOR(1 + RAND() * user_count); SET pid = FLOOR(1 + RAND() * product_count); INSERT INTO orders (user_id, product_id, order_date, amount, note) VALUES ( uid, pid, DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 365 * 5) DAY), ROUND(RAND() * 100, 2), rand_string(20) ); SET i = i + 1; END WHILE; END$$ DELIMITER ; -- 插入 10000 用户 CALL insert_users(10000); -- 插入 1000 产品 CALL insert_products(1000); -- 插入 100000 订单 CALL insert_orders(100000);
上面测试数据，产生3张表
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 14 Server version: 8.0.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h'forhelp. Type '\c' to clear the current input statement. mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | orders | | products | | users | +----------------+ 3 rows inset (0.03 sec) mysql>
SELECT user_id FROM users WHERE user_id in (1,...,1000) ORDER BY signup_date DESC LIMIT 900; SELECT user_id,user_name FROM orders WHERE id IN (user_id);
SELECT user_id FROM users WHERE user_id in (1,.....,1000) ORDER BY rand() DESC LIMIT 1;
排序不要使用rand()函数
其实如果是想抽取一个随机的数据并给付结果，是可以通过下面的方案来进行的。也就是给一个确定的值。不要在ORDER BY 中进行计算。具体的方案是
explain SELECT user_id FROM users WHERE user_id in (1,999,34,23,56,564,1000) ORDER BY rand() DESC LIMIT 1;
可以撰写成下方的样子，数字7是可以改变的，在有多少值的情况下，可以改成多少。
explain SELECT user_id, username FROM users WHERE user_id = ( SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX('1,999,34,23,56,564,1000', ',', n), ',', -1) AS UNSIGNED) FROM ( SELECT FLOOR(1 + RAND() * 7) AS n ) AS rand_pos );
