功能:创建40个表,分别为:
block_0-block_9
tx_0-tx_9;
input_0-input_9;
output_0-output_9;
BEGIN
DECLARE `i` int(11);
DECLARE `block_sql` VARCHAR(2560);
DECLARE `block_t` VARCHAR(50);
DECLARE `tx_sql` VARCHAR(2560);
DECLARE `tx_t` VARCHAR(50);
DECLARE `input_sql` VARCHAR(2560);
DECLARE `input_t` VARCHAR(50);
DECLARE `output_sql` VARCHAR(2560);
DECLARE `output_t` VARCHAR(50);
set `i`=0;
WHILE `i`< 10 DO
set block_t=concat('block_',i);
set tx_t=concat('tx_',i);
set input_t=concat('input_',i);
set output_t=concat('output_',i);
#create table block_i
set @block_sql=concat('CREATE TABLE ' ,block_t ,'(
`id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`file_id` INT(10) UNSIGNED,
`blk_hash` VARCHAR(160) NOT NULL,
`merkle_hash` VARCHAR(160) NOT NULL,
`diff_target` INT(20) UNSIGNED,
`nonce` INT(20) UNSIGNED,
`pre_blk_hash` VARCHAR(160) NOT NULL,
`witness_root` VARCHAR(160) NOT NULL,
`block_time` VARCHAR(50),
`version` INT(20) UNSIGNED,
`work` BigInt UNSIGNED,
`tx_num` int(20) unsigned,
`save_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `ix_file_id` (`file_id`),
INDEX `ix_blk_hash` (`blk_hash`)
)
COLLATE=\'utf8_general_ci\'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED;');
#create table tx_t
set @tx_sql=concat('CREATE TABLE ', tx_t ,' (
`id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`tx_hash` VARCHAR(160) NOT NULL,
`w_tx_hash` VARCHAR(160) NOT NULL,
`input_num` INT(10) UNSIGNED ,
`output_num` INT(10) UNSIGNED,
`blk_hash` VARCHAR(160) NOT NULL,
`save_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `ix_tx_hash` (`tx_hash`),
INDEX `ix_blk_hash` (`blk_hash`)
)
COLLATE=\'utf8_general_ci\'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED;');
#create table input_t
set @input_sql=concat('CREATE TABLE ',input_t ,' (
`id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`tx_hash` VARCHAR(160) NOT NULL,
`in_idx` int(10) unsigned,
`input_script` TEXT NOT NULL,
`save_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `ix_tx_hash` (`tx_hash`)
)
COLLATE=\'utf8_general_ci\'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED;');
#create table output_t
set @output_sql=concat('CREATE TABLE ',output_t ,' (
`id` INT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`tx_hash` VARCHAR(160) NOT NULL,
`out_idx` int(10) unsigned,
`script_pubkey` TEXT NOT NULL,
`addr` VARCHAR(160) NOT NULL,
`save_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `ix_address` (`addr`),
INDEX `ix_tx_hash` (`tx_hash`)
)
COLLATE=\'utf8_general_ci\'
ENGINE=InnoDB
ROW_FORMAT=COMPRESSED
;');
#execute now!
PREPARE create_stmt FROM @block_sql;
EXECUTE create_stmt;
PREPARE create_stmt FROM @tx_sql;
EXECUTE create_stmt;
PREPARE create_stmt FROM @input_sql;
EXECUTE create_stmt;
PREPARE create_stmt FROM @output_sql;
EXECUTE create_stmt;
SET `i`= `i`+1;
END WHILE;
END