分库分表初始化建表

mysql分库分表的建库建表

假设场景使用shareing-jdbc 实现分库分表，版本大于5.7，存储节点为1

1.初始化建库

-- 1. 先删除存储过程
DROP PROCEDURE IF EXISTS `create_databases`;
-- 2. 结束符改为$
DELIMITER $
-- 3. 创建存储过程
CREATE PROCEDURE create_databases ()
BEGIN
DECLARE `@i` INT (11);
DECLARE `@createSql` VARCHAR (2560);
SET `@i` = 10;
WHILE `@i` < 32 DO -- 创建32个库
-- 创建库
SET @createSql = CONCAT( "CREATE DATABASE IF NOT EXISTS contrimetrics_", `@i`, " DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" );
PREPARE stmt FROM @createSql;
EXECUTE stmt;
SET `@i` = `@i` + 1;
END WHILE;
END $
-- 4. 结束符改为;
DELIMITER ;
-- 5. 执行存储过程
CALL `create_databases`();
-- 6. 删除存储过程
DROP PROCEDURE IF EXISTS `create_databases`;

2.初始化建表

-- 1. 先删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;
-- 2. 结束符改为$
DELIMITER $
-- 3. 创建存储过程
CREATE PROCEDURE create_tables ()
BEGIN
DECLARE `@dbi` INT (11);
DECLARE `@usedb` VARCHAR (2500);
-- 不能在 while中声明变量，只能在begin第一句声明变量
DECLARE `@i` INT (11);
DECLARE `@createSql` VARCHAR (25600);

-- 数据库在哪里开始
SET `@dbi` = 10;

WHILE `@dbi` < 32 DO -- 使用数据库
--  set @userdb = CONCAT("USE contrimetrics_",`@dbi`,";");
--   PREPARE stmt FROM @userdb;
--  EXECUTE stmt;
--  DEALLOCATE PREPARE stmt;

    SET `@i` = 0;
    WHILE `@i` < 32 DO -- 创建x张表
    -- 创建表
    SET @createSql = CONCAT("CREATE TABLE IF NOT EXISTS ","contrimetrics_",`@dbi`,".","original_wos_data_", `@i`, " (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `publication_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `authors_a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `book_authors` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `book_editors` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `book_group_authors` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author_full_names` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `book_author_full_names` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `group_authors` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `article_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `Source_title` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `book_series_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `book_series_subtitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `language_l` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `document_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `conference_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `conference_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `conference_location` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `conference_sponsor` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `conference_host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `author_keywords` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `keywords_plus` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `abstract_a` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `addresses_a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `affiliations_a` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `reprint_addresses` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `email_addresses` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `researcher_ids` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `orcds` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `funding_orgs` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `funding_name_preferred` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `funding_text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `cited_references` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `cited_reference_count` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `times_cited_wos_core` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `times_cited_all_databases` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `day_usage_count` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `since_usage_count` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publisher_p` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publisher_city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publisher_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `issn_i` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `eissn_e` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `isbn_i` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `journal_abbreviation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `journal_iso_abbreviation` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publication_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `publication_year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `volume_v` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `issue_i` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `part_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `supplement` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `special_issue` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `meeting_abstract` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `start_page` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `end_page` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `article_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `doi_d` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `doi_link` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `book_doi` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `early_access_date` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `number_of_pages` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `wos_categories` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `web_of_science_index` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `research_areas` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  `ids_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `pubmed_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `open_access_designations` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `highly_cited_status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `hot_paper_status` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `date_of_export` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `unique_wos_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `year_a` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `year_b` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `web_of_science_record` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `spare_field_o` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `spare_field_t` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `spare_field_s` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `create_date` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
  `modify_date` timestamp(0) NULL DEFAULT NULL,
  `sts` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT 'Y',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `Source_title_index`(`Source_title`(191)) USING BTREE,
  INDEX `unique_wos_id_index`(`unique_wos_id`) USING BTREE,
  INDEX `DOISELECT`(`doi_d`(191)) USING BTREE,
  INDEX `artbic`(`article_title`(191)) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    " );
    PREPARE stmt FROM @createSql;
    EXECUTE stmt;
    SET `@i` = `@i` + 1;
    END WHILE;

SET `@dbi` = `@dbi` + 1;
END WHILE;

END $
-- 4. 结束符改为;
DELIMITER ;
-- 5. 执行存储过程
CALL `create_tables`();
-- 6. 删除存储过程
DROP PROCEDURE IF EXISTS `create_tables`;

分库分表初始化建表

文章评论

归档

分类