MySQL

  • 1
CREATE TABLE item (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  description VARCHAR(30),
  price INT UNSIGNED,
  created_at DATETIME
);

INSERT INTO item () VALUES ();
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;
INSERT INTO item (id) SELECT 0 FROM item;

UPDATE item SET
  name = CONCAT('item-', id),
  description = SUBSTRING(MD5(RAND()), 1, 30),
  price = CEIL(RAND() * 10000),
  created_at = ADDTIME(CONCAT_WS(' ','2017-01-01' + INTERVAL RAND() * 180 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));
  • 2
CREATE TABLE t1 (id int, score int);

delimiter //
CREATE PROCEDURE insert_random_data()
BEGIN
    SET @limit = 20;
    SET @pos = 0;
    WHILE @limit > @pos DO
        insert into t1 values 
        (FLOOR(RAND() * 100000), FLOOR(RAND() * 100000)), 
            (FLOOR(RAND() * 100000), FLOOR(RAND() * 100000)),
            (FLOOR(RAND() * 100000), FLOOR(RAND() * 100000)),
            (FLOOR(RAND() * 100000), FLOOR(RAND() * 100000)),
            (FLOOR(RAND() * 100000), FLOOR(RAND() * 100000));
        SET @pos = @pos + 1;
    END WHILE;
END
//
delimiter ;

CALL insert_random_data();

DROP PROCEDURE insert_random_data;

PostgreSQL

CREATE TABLE tables (
   user_id INTEGER,
   affiliate_code VARCHAR(50),
   timestamp timestamp not null default current_timestamp
);

INSERT INTO tables
(
user_id,
affiliate_code,
timestamp
)
SELECT
   s as user_id,
   'u_' || s as user_code,
   CAST('2017-1-1' as TIMESTAMP) + CAST(s.a ||' seconds' as interval) as timestamp
FROM
   GENERATE_SERIES(1,1000) as s(a);

Oracle

with SQL*Plus

set pages 200
set lines 200

create table test_random (col1 number,col2 number, col3 varchar2(100), col4 number);

DECLARE
TYPE tbl_ins IS TABLE OF test_random%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins   tbl_ins;
BEGIN
dbms_random.SEED(1);
  FOR i IN 1 .. 1000000 LOOP
   w_ins(i).col1 := i;                                     
   w_ins(i).col2 := mod(i,3);
   w_ins(i).col3 := 'ABC'||dbms_random.string('x',10);
   w_ins(i).col4 := ceil(dbms_random.value(1, 10000000000));
  END LOOP;
  FORALL i in 1 .. 1000000 INSERT INTO test_random VALUES w_ins(i);
  COMMIT;
END;
/

select * from test_random where ROWNUM <= 5 order by col1;

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です