Mysql数据库字段设计

约定不等于承诺〃 2022-02-14 01:54 440阅读 0赞

## 前言 ##

#### [博主github][github] ####

#### [博主个人博客http://blog.healerjean.com][http_blog.healerjean.com] ####

@Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private Long adminId; //创建者id
    
    private String userName ; //新客户名称【id】邮箱
    
    private BigDecimal balance; //老客户金额
    
    private Integer auditStatus ; //审核状态 0 待审核 1 审核通过
    
    private Date cdate;
    
    private Date udate;
    
    
    
    create table tableTest(
    	id BIGINT(16) unsigned not null auto_increment,
    	adminId    BIGINT(16) default null,
    	userName varchar(128) default null,
    	auditStatus tinyint(1) not NULL  default 1,
    	balance    DECIMAL(19,4),
    	cdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        udate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        unique key key_ adminId(adminId ),
        unique index index_ userName (userName),
        PRIMARY key (id)
    );

### 1、表名设计 ###

尽量使用 项目名\_模块名\_表名

原因：  
项目名，因为我们可能一个数据库对应多个项目，这样容易区分  
模块名，能够清晰明了的知道是哪个模块的表

### 2、字段名设计 ###

使用下划线，不要使用大小写组合，原因自己理解吧，兄弟

### 3、字段设计 ###

#### 设计原则 ####

##### 1、字段尽量不能设置null，这样不方便建立索引，为了防止为null，我们可以给赋予初始值，今后建议 default ‘’,给一个空串，空串不占内存空间，NULL是占内存空间的 #####

##### 2、实体设计的时候，记得给初始值,防止不能直接插入数据库中数据 #####

private BigDecimal totalBalance = BigDecimal.ZERO

#### 3.1、日期设计 ####

**JDBC映射**

**LocalTime** 对应 \*\*time \*\*只包括时间

**LocalDate** 对应 \*\*date \*\* 只包括日期

**LocalDateTime** 对应 \*\*timestamp datetime \*\*包括日期和时间

timestamp 多个日期，如果可能为空，则建议使用datetime（默认值建议设置为）
    0001-01-01 00:00:00，因为0000-00-00 00:00:00mysql不能保存，而且会报错，
    
    普通字段不要设置为timestamp，timestamp列必须有默认值，默认值可以为“0000-00-00 00:00:00”，但不能为null。如果我们在save实体的时候，没有给相关timestamp设置值，那么他就会自动由mysql将当前时间设置进去
    
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP",insertable = true,updatable = false)
    @ApiModelProperty(hidden = true)
    private Date cdate;
    
    @UpdateTimestamp
    @Temporal(TemporalType.TIMESTAMP)
    @ApiModelProperty(hidden = true)
    private Date udate;

#### 3.2、小数数字设计 decimall ####

mysql中的decimal字段，声明语法为DECIMAL(M,D)。
    DECIMAL(M,D)中D值的是小数部分的位数，若插入的值未指定小数部分或者小数部分不足D位则会自动补到D位小数，若插入的值小数部分超过了D为则会发生截断，截取前D位小数(四舍五入截取)。M值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过M-D位，否则不能成功插入，会报超出范围的错误。
    
    先保证小数点，再保证整数
    decimal精度类型，函数完整格式DECIMAL(M,D)
    M是是只的最大精度数位，1-65
    D是小数点右侧数位0-30
    举例说明，11615.23653234568这个数存你说的三个格式
    decimal：11615
    decimal(3)：999
    decimal(3,2)：9.99
    decimal(10,5)11615.23653
    超出精度范围的数会被强制进位并只显示数据类型定义的格式

#### 3.3、数字设计 ####

##### 3.3.1、mysql 类型有符号范围和无符号范围 #####

> 带符号和无符号,顾名思义,就是是否有正负之分:
> 
> 比如8位的二进制,
> 
> 如果带符号,需要用1位表示符号(1表示负数,0表示正),剩下7位表示数据.  
> 那么表示范围是-127—127(包括-0和+0).
> 
> 如果不带符号,8位全部表示数据,  
> 那么表示范围是 0–256
> 
> 理解下了的话，就是无符号都是正数 ，所以主键自增Id我们一般都设计为无符号的
> 
>     `id` bigint(16) unsigned NOT NULL AUTO_INCREMENT,

##### 3.3.2、各个数据类型的长度以及默认 #####

> float和double都是带符号

> 整型的每一种都分无符号（unsigned）和有符号（signed）两种类型，在默认情况整型变量都是有符号的类型

![1556179030028][]

##### 3.3.3、int(M) (用于提示开发者长度) #####

这个长度`M`并不代表允许存储的宽度,`int（M）`,也就是`int（3`）和`int（11）`能够存储的数据是一样的

只有联合zerofill参数才能有意义，否则`int（3）`和`int（11）`没有任何区别。

###### 1、不加zeroffill没有区别 ######

create table test_int
        (
          id   int(3)  unsigned  not null,
          uid  int(11) unsigned  not null,
          uuid int     unsigned  not null
        );
    #插入数据 
    insert into test_int
    values (4294967295, 4294967295, 4294967295);
    
    #查询数据，发现没有什么区别
    select * from test_int;
    +------------+------------+------------+
    | id         | uid        | uuid       |
    +------------+------------+------------+
    | 4294967295 | 4294967295 | 4294967295 |
    +------------+------------+------------+
    1 row in set (0.00 sec)

###### 2、有了zeroffill 不足会自动补0 ######

create table test_int1
    (
          id   int(3) unsigned zerofill  not null,
          uid  int(11) unsigned zerofill not null,
          uuid int unsigned zerofill     not null
    );
    #插入数据
    insert into test_int1
    values (4294967295, 4294967295, 4294967295);
    
    insert into test_int1
    values (1, 4294967295, 110000);
    
    #查询数据 发现前面的不足长度的右0了，当然不能使用idea测试，idea没有显示0
    mysql> select * from test_int1;
    +------------+-------------+------------+
    | id         | uid         | uuid       |
    +------------+-------------+------------+
    | 4294967295 | 04294967295 | 4294967295 |
    |        001 | 04294967295 | 0000110000 |
    +------------+-------------+------------+
    2 rows in set (0.02 sec)

###### 3、当使用zerofill 时，默认会自动加unsigned（无符号），zerofill默认为int(10) ######

create table test_int2
    (
          id   int(3) unsigned zerofill  not null,
          uid  int  zerofill not null,
          uuid int unsigned zerofill     not null
    );
    
    # 下面的不能执行成功，以为无符号的都是正数
    insert into test_int2
    values (1, -4294967295, 110000);
    
    insert into test_int2
    values (1, 12345678, 110000);
    
    mysql> select * from test_int2;
    +-----+------------+------------+
    | id  | uid        | uuid       |
    +-----+------------+------------+
    | 001 | 0012345678 | 0000110000 |
    +-----+------------+------------+

##### 3.3.4、char(M),varcahr(M) #####

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，`MySQL就会在它的右边用空格字符补足`．（在检索操作中那些填补出来的空格字符将被去掉）

varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（**即总长度为L+1字节**）

##### 3.3.5、boolean #####

boolean值用1代表TRUE，0代表FALSE。boolean在mysql里的类型为tinyint(1)。mysql里有四个常量：true,false,TRUE,FALSE分别代表1,0,1,0。

private Boolean loan;
    	
    tinyint(1) NOT NULL COMMENT '是否借款 true/false 1/0',

#### 3.4、索引设计 ####

*  普通索引前缀：idx\_索引字段名
 *  唯一索引前缀：uk\_索引字段名

每个表的索引个数尽量少于5个，避免创建重复冗余索引；每个组合索引尽量避免超过3个字段

![ContactAuthor][]

[github]: https://github.com/HealerJean
[http_blog.healerjean.com]: http://HealerJean.github.io
[1556179030028]: /images/20220214/1082ee5c4cb64b5589e6b435c8ef101a.png
[ContactAuthor]: /images/20220214/2046564716314a7086d9e174c0db0fee.png