create table 的最佳方式

#################################

 

 

存储引擎	engine	=innodb
主键自增值	auto_increment	=1
字符集	charset	=utf8mb4
排序规则	collate	=utf8mb4_bin
备注	comment	='表的相关说明语句'

 

 

 

 

 

 

 

一、创建表结构

 

mysql> show create table order_ipad \G
*************************** 1. row ***************************
       Table: order_ipad
Create Table: CREATE TABLE `order_ipad` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `uuid` bigint unsigned NOT NULL DEFAULT '0' COMMENT '用户id',
  `order_id` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '订单id',
  `name` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '商品名称',
  `type` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '状态 1:增加 2:减少',
  `price` int unsigned NOT NULL DEFAULT '0' COMMENT '钱',
  `title` varchar(64) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '标题',
  `status` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '状态 1:进行中 2:已完成 3：已退款 4:已失败 99:异常',
  `source` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '来源 1:任务系统 2:金币系统 3:活动系统',
  `end_time` bigint unsigned NOT NULL DEFAULT '0' COMMENT '过期时间',
  `record_time` bigint unsigned NOT NULL DEFAULT '0' COMMENT '记录发生时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `refund_time` bigint unsigned NOT NULL DEFAULT '0' COMMENT '退款时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_orderid` (`order_id`),
  KEY `idx_uuid_name` (`uuid`,`name`(10)),
  KEY `idx_uuid_title` (`uuid`,`title`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用户小游戏金币收支记录表'
1 row in set (0.01 sec)

mysql>

 

############################################################################################
1、一张表必须有主键。一般都是与业务无关的unsigned自增主键。

2、字段一般都要有not null约束；

   除主键外，一般还都有default约束，通常整型的默认值是0，字符串的默认值为''；

   每个字段和表都有comment说明,用以说明每个字段的含义和表的用途。

3、通常需要在建表的时候就能预估下常用的查询，以便创建合适的索引来加快查询、删除、更新速度。

4、表的存储引擎一般都是innodb，表的默认字符集一般都是utf8mb4，自增列初始值。

5、尽量不要用外键约束！！！

 

 此外还需注意：

1、如果字段为int 或者varchar类型，那么要设置字段类型为 not null 并且设置default

2、如果字段为text，或者longtext,则既不需要设置not null,也不需要手动设置default 的值

3、关于text字段不能有默认值的问题，这个只针对于手动增加的default属性，如果创建字段不设置default的话，mysql会默认加上一个默认值Null

 

 

 

 

二、修改表结构

 

1、修改表的字符集和字符序

alter table table_name convert to character set utf8mb4 collate utf8mb4_general_ci;

 

创建相同结构的表：

((none)) > use google
Database changed
Fri Sep  4 15:10:25 2020(google) > create table user like apple.user;

 

 

建表反例：

 > show create table tasks\G
*************************** 1. row ***************************
       Table: tasks
Create Table: CREATE TABLE `tasks` (
  `id` varchar(255) NOT NULL,
  `created_date` varchar(255) NOT NULL,
  `datasource` varchar(255) NOT NULL,
  `payload` longblob NOT NULL,
  `pay` longblob NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_date` (`active`,`created_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.08 sec)


# 第一：主键是varchar类型，不及自增主键bigint；

# 第二：联合索引顺序不对，且active字段是地选择性，因此，只需要对created_date字段创建索引即可；

# 第三：字段没有注释，不知道这个表是干嘛的；

# 字段不给默认值，不过这个问题不算大；

 

 

 

【强制】表必备6个字段：pk_id，id，created_time，modified_time，deleted_time，is_deleted
deleted_time    必须    datetime(3)    not null  default '9999-12-31 23:59:59.000'

CREATE TABLE `user` (
`pk_id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`id` int(18) NOT NULL COMMENT '业务id 也可以自增 根据实际需要修改', 
`name` varchar(100) NOT NULL COMMENT '姓名',
`address` varchar(100) NOT NULL COMMENT '地址',
`is_deleted` tinyint(4) DEFAULT NULL COMMENT '是否删除',
`created_time` datetime NOT NULL COMMENT '创建时间',
`modified_time` datetime NOT NULL COMMENT '修改时间',
`deleted_time` datetime(3) NOT NULL DEFAULT '9999-12-31 23:59:59.000' COMMENT '逻辑删除时间',
PRIMARY KEY (`pk_id`),
UNIQUE KEY `uniqu_id` (`id`),
KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='说明';

 

 -- 建表样例：
CREATE TABLE `document` (
`pk_id` bigint(18) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`id` int(18) NOT NULL COMMENT '业务id',
`title` varchar(100) NOT NULL COMMENT '标题',
`keywords` varchar(100) NOT NULL COMMENT '关键字',
`preview_img` varchar(255) NOT NULL COMMENT '预览KanoIds',
`cover` varchar(100) NOT NULL COMMENT '封面图，kanoId',
`file_id` varchar(20) NOT NULL COMMENT '文档页编号',
`version` varchar(20) NOT NULL COMMENT '最新版本号',
`user_name` varchar(20) NOT NULL COMMENT '权属人用户名称',
`user_id` varchar(20) NOT NULL COMMENT '负责人/权属人OA用户id',
`price` int(11) DEFAULT NULL COMMENT '价格',
`is_deleted` tinyint(4) DEFAULT NULL COMMENT '是否删除',
`gmt_created` datetime NOT NULL COMMENT '创建时间',
`gmt_modified` datetime NOT NULL COMMENT '修改时间',
`gmt_deleted` datetime(3) NOT NULL DEFAULT '9999-12-31 23:59:59.000' COMMENT '逻辑删除时间',
PRIMARY KEY (`pk_id`),
UNIQUE KEY `uniq_id` (`id`),
KEY `idx_title` (`title`)
KEY `idx_gmtCreated` (`gmt_created`),
KEY `idx_gmtModified` (`gmt_modified`)
) DEFAULT CHARSET=utf8mb4 COMMENT='文档表';

 

#####################################