[大数据]mysql 建库建表模板

写在前面

mysql8的数据导入到mysql5 可能会有编码不支持

将 utf8mb4_unicode_ci 全部替换成 utf8_general_ci

再将 utf8mb4 全部替换成 utf8

在执行导入即可

mysql5

建库模板

CREATE DATABASE `basedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE USER `base`@`localhost` IDENTIFIED BY 'password';

GRANT Delete, Insert, Select, Update ON `basedb`.* TO `base`@`localhost`;

FLUSH PRIVILEGES;

建表模板

CREATE TABLE `t_base` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_deleted` tinyint(1) DEFAULT 0 COMMENT '是否删除：1删除，0未删除',

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8  COMMENT='模板表';

mysql8

建库模板

CREATE DATABASE `basedb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER `base`@`localhost` IDENTIFIED BY 'password';

GRANT Delete, Insert, Select, Update ON `basedb`.* TO `base`@`localhost`;

FLUSH PRIVILEGES;

建表模板

CREATE TABLE `t_base` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'Id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modify_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `is_deleted` tinyint(1) DEFAULT 0 COMMENT '是否删除：1删除，0未删除',

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4  COMMENT='模板表';