1.通過(guò)shell腳本批量建表

#!/bin/bash
#批量新建數(shù)據(jù)表
for y in {1..53};do
 mysql -uroot  -pTimevale123 -P3306  -e "use seal; create table seal_sign_log_2020_$y(
id bigint(64) unsigned NOT NULL COMMENT 'id',
  doc_name varchar(512) NOT NULL DEFAULT '' COMMENT '文檔名稱',
  seal_code varchar(32) NOT NULL DEFAULT '' COMMENT '印章編碼',
  sign_log_id varchar(64) NOT NULL DEFAULT '' COMMENT '簽署日志id',
  account_id bigint(11) DEFAULT NULL COMMENT '用戶id',
  account_unique_id varchar(50) DEFAULT NULL COMMENT '用戶唯一標(biāo)識(shí)',
  account_name varchar(100) DEFAULT NULL COMMENT '姓名',
  organize_id bigint(11) DEFAULT NULL COMMENT '組織機(jī)構(gòu)id',
  organize_full_name varchar(100) DEFAULT NULL COMMENT '組織機(jī)構(gòu)全稱',
  sign_time datetime DEFAULT NULL COMMENT '簽署時(shí)間',
  sign_type int(2) DEFAULT NULL COMMENT '簽署類型：1-PDF簽署，2-OFD簽署',
  sign_mac varchar(64) NOT NULL DEFAULT '' COMMENT '簽署設(shè)備mac',
  sign_ip varchar(15) DEFAULT NULL COMMENT '簽署IP地址',
  create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
  sign_task_id varchar(36) DEFAULT NULL COMMENT '簽署任務(wù)id',
  auth_way int(2) NOT NULL DEFAULT '0' COMMENT '意愿認(rèn)證方式，1:ukey意愿認(rèn)證 2:后臺(tái)靜默授權(quán)',
  project_name varchar(100) DEFAULT '' COMMENT '項(xiàng)目名稱',
  PRIMARY KEY (id) USING BTREE,
  KEY idx_create_time (create_time) USING BTREE,
  KEY idx_sign_time (sign_time) USING BTREE,
  KEY idx_unique_id (account_unique_id) USING BTREE,
  KEY idx_account_name (account_name) USING BTREE,
  KEY idx_organize_full_name (organize_full_name) USING BTREE,
  KEY idx_sign_type (sign_type) USING BTREE,
  KEY index_sign_task_id (sign_task_id) USING BTREE,
  KEY idx_project_name (project_name),
  KEY idx_org_id_seal_code (seal_code,organize_id),
  KEY idx_project_test (project_name,seal_code)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='簽章日志';"
done

2.批量刪表

使用

Select CONCAT( 'drop table ', table_name, ';' ) FROM information_schema.tables Where table_name LIKE 'seal_sign_log_%';

可生成drop表語(yǔ)句,然后可以批量刪除:

drop table seal_sign_log_2020_1 ...;