三个主流数据库Oracle、MySQL和SQL Server的“单表造数

oracle

1.创建表

1.  
   CREATE TABLE "YZH2_ORACLE" (
2.  
   "VARCHAR2_COLUMN" VARCHAR2(20) NOT NULL ENABLE,
3.  
   "NUMBER_COLUMN" NUMBER,
4.  
   "DATE_COLUMN" DATE,
5.  
   "CLOB_COLUMN" CLOB,
6.  
   "BLOB_COLUMN" BLOB,
7.  
   "BINARY_DOUBLE_COLUMN" BINARY_DOUBLE,
8.  
   "BINARY_FLOAT_COLUMN" BINARY_FLOAT,
9.  
   "CHAR_COLUMN" CHAR(1),
10.  
    "CHAR_VARYING_COLUMN" VARCHAR2(20),
11.  
    "DEC_COLUMN" NUMBER(*, 0),
12.  
    "DECIMAL_COLUMN" NUMBER(*, 0),
13.  
    "DOUBLE_PRECISION_COLUMN" FLOAT(126),
14.  
    "CHARACTER_COLUMN" CHAR(1),
15.  
    "CHARACTER_VARYING_COLUMN" VARCHAR2(20),
16.  
    "FLOAT_COLUMN" FLOAT(126),
17.  
    "INT_COLUMN" NUMBER(*, 0),
18.  
    "INTEGER_COLUMN" NUMBER(*, 0),
19.  
    "NATIONAL_CHAR_COLUMN" NCHAR(1),
20.  
    "NATIONAL_CHAR_VARYING_COLUMN" NVARCHAR2(20),
21.  
    "NATIONAL_CHARACTER_COLUMN" NCHAR(1),
22.  
    "NATIONAL_CHARACTER_VARY_COLUMN" NVARCHAR2(20),
23.  
    "NCHAR_COLUMN" NCHAR(1),
24.  
    "NCHAR_VARYING_COLUMN" NVARCHAR2(20),
25.  
    "NCLOB_COLUMN" NCLOB,
26.  
    "NUMERIC_COLUMN" NUMBER(*, 0),
27.  
    "NVARCHAR2_COLUMN" NVARCHAR2(20),
28.  
    "RAW_COLUMN" RAW(20),
29.  
    "REAL_COLUMN" FLOAT(63),
30.  
    "SMALLINT_COLUMN" NUMBER(*, 0),
31.  
    "TIMESTAMP_COLUMN" TIMESTAMP (6),
32.  
    "VARCHAR_COLUMN" VARCHAR2(20)
33.  
    )

预设游标10000（必要时）

alter system set open_cursors = 10000;

 模拟新增10万条数据

1.  
    
2.  
    
3.  
    
4.  
   DECLARE I number := 0;
5.  
   BEGIN
6.  
   FOR I IN 0..100000 LOOP
7.  
   INSERT INTO YZH2_ORACLE (
8.  
   VARCHAR2_COLUMN, NUMBER_COLUMN, BINARY_DOUBLE_COLUMN,
9.  
   BINARY_FLOAT_COLUMN, CHAR_VARYING_COLUMN,
10.  
    DEC_COLUMN, DECIMAL_COLUMN, DOUBLE_PRECISION_COLUMN,
11.  
    CHARACTER_VARYING_COLUMN, FLOAT_COLUMN,
12.  
    INT_COLUMN, INTEGER_COLUMN, NATIONAL_CHAR_VARYING_COLUMN,
13.  
    NATIONAL_CHARACTER_VARY_COLUMN,
14.  
    NCHAR_VARYING_COLUMN, NUMERIC_COLUMN,
15.  
    NVARCHAR2_COLUMN, REAL_COLUMN, SMALLINT_COLUMN,
16.  
    VARCHAR_COLUMN
17.  
    )
18.  
    VALUES (I, I, I, I, I, I, I, I, I, I, I, I, I, I, I, I,I, I, I, I);
19.  
    END LOOP;
20.  
    COMMIT;
21.  
    END;

 1.1 oracle一条sql数据生成100万条数据

1.  
   SELECT
2.  
   rownum AS id,
3.  
   to_char(sysdate rownum / 24 / 3600, 'yyyy-mm-dd hh24:mi:ss') AS inc_datetime,
4.  
   trunc(dbms_random.value(0, 100)) AS random_id,
5.  
   dbms_random.string('x',20) AS random_string
6.  
   FROM
7.  
   dual
8.  
   CONNECT BY
9.  
   LEVEL <= 1000000; #可传参数

2.SQL SERVER

创建测试表

1.  
    
2.  
   CREATE TABLE dbo.yzh2_ms_checker (
3.  
   BIGINT_COLUMN bigint NULL DEFAULT (NULL),
4.  
   BINARY_COLUMN binary(64) NULL DEFAULT (NULL),
5.  
   BIT_COLUMN bit NULL DEFAULT (NULL),
6.  
   CHAR_COLUMN char(64) NULL DEFAULT (NULL),
7.  
   CHAR_MAX_COLUMN char(128) NULL DEFAULT (NULL),
8.  
   DATE_COLUMN date NULL DEFAULT (NULL),
9.  
   DATETIME_COLUMN datetime NULL DEFAULT (NULL),
10.  
    DECIMAL_COLUMN decimal(18,0) NULL DEFAULT (NULL),
11.  
    FLOAT_COLUMN real NULL DEFAULT (NULL),
12.  
    INT_COLUMN int NULL DEFAULT (NULL),
13.  
    MONEY_COLUMN money NULL DEFAULT (NULL),
14.  
    NCHAR_COLUMN nchar(64) NULL DEFAULT (NULL),
15.  
    NCHAR_MAX_COLUMN nchar(128) NULL DEFAULT (NULL),
16.  
    NTEXT_COLUMN ntext NULL DEFAULT (NULL),
17.  
    NUMERIC_COLUMN numeric(18,0) NULL DEFAULT (NULL),
18.  
    NVARCHAR_COLUMN nvarchar(64) NULL DEFAULT (NULL),
19.  
    NVARCHAR_MAX_COLUMN nvarchar(128) NULL DEFAULT (NULL),
20.  
    REAL_COLUMN real NULL DEFAULT (NULL),
21.  
    SMALLINT_COLUMN smallint NULL DEFAULT (NULL),
22.  
    SMALLMONEY_COLUMN smallmoney NULL DEFAULT (NULL),
23.  
    TEXT_COLUMN text NULL DEFAULT (NULL),
24.  
    TINYINT_COLUMN tinyint NULL DEFAULT (NULL),
25.  
    VARBINARY_COLUMN varbinary(64) NULL DEFAULT (NULL),
26.  
    VARCHAR_COLUMN varchar(64) NULL DEFAULT (NULL),
27.  
    VARCHAR_MAX_COLUMN varchar(128) NULL DEFAULT (NULL),
28.  
    XML_COLUMN xml NULL DEFAULT (NULL)
29.  
    );
30.  
    GO

1.  
    
2.  
   DECLARE @i INT
3.  
   SET
4.  
   @i = 1
5.  
   WHILE @i <=30000 BEGIN
6.  
    
7.  
   INSERT INTO dbo.yzh2_ms_checker (
8.  
   BIGINT_COLUMN, BINARY_COLUMN, BIT_COLUMN,
9.  
   CHAR_COLUMN, CHAR_MAX_COLUMN, DATE_COLUMN,
10.  
    DATETIME_COLUMN, DECIMAL_COLUMN,
11.  
    FLOAT_COLUMN, INT_COLUMN, MONEY_COLUMN,
12.  
    NCHAR_COLUMN, NCHAR_MAX_COLUMN,
13.  
    NTEXT_COLUMN, NUMERIC_COLUMN, NVARCHAR_COLUMN,
14.  
    NVARCHAR_MAX_COLUMN, REAL_COLUMN,
15.  
    SMALLINT_COLUMN, SMALLMONEY_COLUMN,
16.  
    TEXT_COLUMN, TINYINT_COLUMN, VARBINARY_COLUMN,
17.  
    VARCHAR_COLUMN, VARCHAR_MAX_COLUMN,
18.  
    XML_COLUMN
19.  
    )
20.  
    VALUES
21.  
    (
22.  
    @i,1,@i,@i,@i,GETDATE(),GETDATE(),@i,@i,@i,@i,@i,@i,STR(@i),
23.  
    @i,STR(@i),STR(@i),@i,@i,@i,STR(@i),NULL,1,@i,@i,STR(@i)
24.  
    )
25.  
     
26.  
    SET @i = @i 1
27.  
    END;

3.MySQL

创建测试表

1.  
   CREATE TABLE `yzh2_sync` (
2.  
   `bigint_column` bigint(10) NOT NULL AUTO_INCREMENT COMMENT '1',
3.  
   `bit_column` bigint(10) NOT NULL COMMENT '2',
4.  
   `blob_column` blob NOT NULL COMMENT '3',
5.  
   `char_column` char(64) NOT NULL COMMENT '4',
6.  
   `date_column` date NOT NULL COMMENT '5',
7.  
   `datetime_column` datetime NOT NULL COMMENT '6',
8.  
   `decimal_column` decimal(18,9) NOT NULL COMMENT '7',
9.  
   `double_column` double NOT NULL COMMENT '8',
10.  
    `float_column` float NOT NULL COMMENT '10',
11.  
    `int_column` int(11) NOT NULL COMMENT '11',
12.  
    `longblob_column` longblob NOT NULL COMMENT '12',
13.  
    `longtext_column` longtext NOT NULL COMMENT '13',
14.  
    `mediumblob_column` mediumblob NOT NULL COMMENT '14',
15.  
    `mediumint_column` mediumint(9) NOT NULL COMMENT '15',
16.  
    `mediumtext_column` mediumtext NOT NULL COMMENT '16',
17.  
    `smallint_column` smallint(6) NOT NULL COMMENT '18',
18.  
    `text_column` text NOT NULL COMMENT '19',
19.  
    `time_column` time NOT NULL COMMENT '20',
20.  
    `timestamp_column` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '21',
21.  
    `tinyint_column` int(11) NOT NULL COMMENT '22',
22.  
    `tinytext_column` tinytext NOT NULL COMMENT '23',
23.  
    `varchar_column` varchar(32) NOT NULL COMMENT '24',
24.  
    PRIMARY KEY (`bigint_column`)
25.  
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
26.  
     
27.  
     

模拟新增3万条数据

1.  
    
2.  
    
3.  
   DROP PROCEDURE if EXISTS test_insert;
4.  
   DELIMITER //
5.  
   CREATE PROCEDURE test_insert()
6.  
   BEGIN
7.  
   DECLARE y BIGINT DEFAULT 0;
8.  
   WHILE y<30000
9.  
   DO
10.  
     
11.  
    INSERT INTO yzh2_sync (
12.  
    bit_column, blob_column, char_column,
13.  
    date_column, datetime_column, decimal_column,
14.  
    double_column, float_column, int_column,
15.  
    longblob_column, longtext_column,
16.  
    mediumblob_column, mediumint_column,
17.  
    mediumtext_column, smallint_column,
18.  
    text_column, time_column, timestamp_column,
19.  
    tinyint_column, tinytext_column,
20.  
    varchar_column)
21.  
    VALUES(
22.  
    y, y, y,
23.  
    now(), now(), y,
24.  
    y, y, y,
25.  
    y, y,
26.  
    y, y,
27.  
    y, y,
28.  
    y,now(), now(),
29.  
    y, y,
30.  
    y
31.  
    );
32.  
     
33.  
    SET y=y 1;
34.  
    END WHILE ;
35.  
    commit;
36.  
    END //

1.  
   { CALL xag.test_insert() }
2.  
    

这篇好文章是转载于：学新通技术网