MySQL(数据类型和完整约束)

2022-07-21 09:52:38 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

MySQL数据类型

MySQL支持多种数据类型，主要有数值类型、日期/时间类型和字符串类型。

1.数值数据类型
代码语言：javascript复制
包括整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数数据类型FLOAT和DOUBLE、定点小树类型DECIMAL。
2.日期/时间类型
代码语言：javascript复制
包括YEAR、TIME、DATE、DATETIME和TIMESTAMP。
3.字符串类型
代码语言：javascript复制
包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
  1. 1. 整数类型 数值型数据类型主要用来存储数字，MYSQL提供了多种数值数据类型，不同的数据库有不同的取值范围，可以存储的值范围越大，其所存储的控件也会越大。MySQL主要提供整数类型有：TINYTINT、SMALLINT、MEDIUMINT、INT、BIGINT。整数类型属性的字段可以添加AUTO_INCREMENT自增约束条件。下表列出了MySQL中的数值类型。

类型名称

说明

存储需求

TINYINT

很小的整数

1个字节

SMALLINT

小的整数

2个字节

MEDIUMINT

中等大小的整数

3个字节

INT

普通大小的整数

4个字节

BIGINT

大整数

8个字节

从表可知，不同类型整数存储所需的字节数是不同的，占用字节数最小的是TINYINT类型，占用字节最大的是BIGINT类型，相应的占用字节越多的类型所能表示的数值范围越大。根据占用字节数可以求出每一种数据的取值范围，例如TINYINT需要1个字节(8bits)来存储，那么TINYINT无符号数的最大值为2^8-1，即255；TINYINT有符号数的最大值为2^7-1，即127。其它类型如下表所示：

数据类型

有符号

无符号

TINYINT

-128~127

0~255

SMALLINT

-32768~32767

0~65535

MEDIUMINT

-8388608~8388607

0~16777215

INT

-2147483648~2147483647

0~4294967295

BIGINT

-9223372036854775808~9223372036854775807

0~18446744073709551615

　　1.2 浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点类型有两种：单精度（FLOAT）和双精度（DOUBLE）。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M，N）来表示，其中M称为精度，表示总共的位数；N称为标度，是表示小数的位数。下表列出了小数类型存储的需求。

类型名称

说明

存储需求

FLOAT

单精度浮点数

4个字节

DOUBLE

双精度浮点数

8个字节

DECIMAL(M,D),DEC

压缩的”严格”定点数

M 2个字节

DECIMAL类型不同于FLOAT和DOUBLE，DECIMAL实际是以串存放的，DECIMAL可能的最大取值范围与DOUBLE一样，但是其有效的取值范围由M和D的值决定。如果改变M而固定D，则取值范围将随M的变大而变大。

1.3 日期/时间类型

MySQL中有多种表示日期的数据类型，主要有：DATETIME、DATE、TIMESTAMP、TIME和YEAR。下表列出了MySQL日期/时间类型：

类型名称

日期格式

日期范围

存储需求

YEAR

YYYY

1901~2155

1字节

TIME

HH:MM:SS

-838:59:59~838:59:59

3字节

DATE

YYYY-MM-DD

1000-01-01~9999-12-3

3字节

DATETIME

YYYY-MM-DD HH:MM:SS

1000-01-01 00:00:00~9999-12-31 23:59:59

8字节

TIMESTAMP

YYYY-MM-DD HH:MM:SS

1970-01-01 UTC ~ 2038-01-19 03:14:07 UTC

4字节

　　1.4 字符串类型

字符串类型用来存储字符串数据，除了可以存储字符串之外，还可以存储其它数据，比如图片和声音二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。下表列出了字符串数据类型。

类型名称

说明

存储需求

CHAR(M)

固定长度非二进制字符串

M字节,1<=M<=255

VARCHAR(M)

变长非二进制字符串

L 1字节,在此L<=M和1<=M<=255

TINYTEXT

非常小的非二进制字符串

L 1字节，在此L<2^8

TEXT

小的非二进制字符串

L 2字节，在此L<2^16

MEDIUMTEXT

中等大小的非二进制字符串

L 3字节,在此L<2^32

LONGTEXT

大的非二进制字符串

L 4字节,在此L<2^24

ENUM

枚举类型,只能有一个枚举字符串值

1或2个字节,取决于枚举值数目(最大值65535)

SET

一个设置,字符串对象可以有0个或多个SET成员

1,2,3,4或8个字节，取决于集合成员的数量（最多64个成员）

　　1.5 二进制类型

MySQL支持两类字符型数据：文本字符串和二进制字符串。MySQL中存储二进制字符串数据类型有：BIT、BINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。下表列出了二进制数据类型：

类型名称

说明

存储需求

BIT(M)

位字段类型

大约(M 7)/8个字节

BINARY(M)

固定长度二进制字符串

M个字节

VARBINARY(M)

可变长度二进制字符串

M 1个字节

TINYBLOB(M)

非常小的BLOB

L 1个字节,在此L<2^8

BLOB(M)

小BLOB

L 2字节,在此L<2^16

MEDIUMBLOB(M)

中等大小的BLOB

L 3字节,在此L<2^24

LONGBLOB(M)

非常大的BLOB

L 4字节,在此L<2^32

如何选择数据类型

MySQL提供了大量的数据类型，为了优化存储，提高数据库性能，在任何情况下均应该使用精确的类型。即在所有可以表示该列值的类型中，该类型使用的存储最少。

2.1 整数和浮点数

如果不需要小数部分，则使用整数来保存数据；如果需要小数部分，则使用浮点数来表示。对于浮点数据列，存入的数值会对该列定义的小数位进行四舍五入。例如，如果列的值范围为1-99999，若使用整数，则MEDIUMINT UNSIGNED是最好的类型；若需存储小数，则使用FLOAT类型。 浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类型高，因此，如要求存储精度较高时，应选择DOUBLE。

2.2 浮点数和定点数

浮点数FLOAT、DOUBLE相对于定点数DECIMAL的优势是：在长度一定的情况下，浮点数能表示更大的数据范围。但是由于浮点数容易产生误差，因此对精确度要求比较高时，建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的，用于定义货币等对精确度要求较高的数据。DECIMAL在MySQL中是以字符串存储的。在数据迁移中，float(M,D)是非标准SQL定义，数据库迁移可能会出现问题，最好不要这样使用。另外两个浮点数进行减法和比较运算时也容易出现问题，因此在进行计算的时候，一定要小心。如果进行数值比较，建议使用DECIMAL类型。

2.3 日期/时间类型

MySQL对于不同种类的日期和时间有多种数据类型，比如YEAR和TIME。如果只需记录年份，则使用YEAR即可，如果只记录时间，则使用TIME类型。 如果同时需要记录日期和时间则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP这个列取值时范围小于DATETIME的取值范围，因此存储范围较大的日期最好使用DATETIME。 TIMESTAMP也有一个DATETIME不具备的属性。默认情况下，当插入一条记录但并没有指定TIMESTAMP这个列值时，MySQL会把TIMESTAMP列设为当前的时间。因此需要插入记录同时插入当前时间时，使用TIMESTAMP是方便的，另外TIMESTAMP在空��上比DATETIME更有效。

2.4 CHAR与VARCHAR之间的特点与选择

CHAR与VARCHAR之间的区别： CHAR是固定长度字符，VARCHAR是可变长度字符；CHAR会自动删除插入数据的尾部空格，VARCHAR不删除尾部空格。 CHAR是固定长度，所以它的处理速度比VARCHAR速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用CHAR类型，反之可以使用VARCHAR类型来实现。

存储引擎对CHAR和VARCHAR的影响：

对于MyISAM存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用存储空间换查询时间。 对于InnoDB存储引擎：使用可变长的数据列，因为InnoDB数据表的存储格式不分固定长度和可变长度，因此使用CHAR不一定比使用VARCHAR更好，但由于VARCHAR是按照实际存的长度存储，比较节省空间，所以对磁盘I/O和数据存储总量比较好。

2.5 ENUM和SET

ENUM只能取单值，它的数据列表示一个枚举集合。它的合法值列表最多有65535个成员。因此，在需要从多个值中选取一个时，可以使用ENUM。比如：性别字段适合定义成ENUM类型，每次只能从’男’ 或 ‘女’中取一个值。 SET可取多值。它的合法取值列表最多允许有64个成员。空字符串也是一个合法的SET值。在需要取多个值的时候，适合使用SET类型，比如：要存储一个兴趣爱好，最好使用SET类型。 ENUM和SET的值是以字符串形式出现的，但在内部，MySQL以数值的形式存储它们。

2.6 BLOB和TEXT

BLOB是二进制字符串，TEXT是非二进制字符串，二者均可存放大容量信息。BLOB主要存储图片、音频信息等，而TEXT只能存储纯文本文件，应分清二者存储的关系。

完整约束

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

分类及详解

代码语言：javascript复制
PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

View Code

类型一 not null和default

not null – 不可空

default默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

代码语言：javascript复制
==================not null====================
mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | YES  |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO   |     | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value



==================default====================
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;



==================综合练习====================
mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
 ------- ------------------------------------ ------ ----- ------------ ------- 
| Field | Type                               | Null | Key | Default    | Extra |
 ------- ------------------------------------ ------ ----- ------------ ------- 
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | NO   |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
 ------- ------------------------------------ ------ ----- ------------ ------- 
mysql> insert into student(name) values('egon');
mysql> select * from student;
 ------ ----- ------ ------------ 
| name | age | sex  | hobby      |
 ------ ----- ------ ------------ 
| egon |  18 | male | play,music |
 ------ ----- ------ ------------

示例验证

类型二 unique（设置唯一约束）

代码语言：javascript复制
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);


方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);


mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
会报错  怎么解决呢？？？
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

设置唯一约束的方法

约束not null和unique结合的神奇之处：

代码语言：javascript复制
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;
 ------- --------- ------ ----- --------- ------- 
| Field | Type    | Null | Key | Default | Extra |
 ------- --------- ------ ----- --------- ------- 
| id    | int(11) | NO   | PRI | NULL    |       |
 ------- --------- ------ ----- --------- ------- 
1 row in set (0.00 sec)

View Code

其实Notnull和unique结合就相当于把上面示例中的id字段设置为主键

代码语言：javascript复制
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0


将主键设置为host和port俩个字段

设置联合唯一主键

类型三 primary key设置主键

primary key 字段的值不为空且唯一 可设置为主键(单列做主键 多列做主键(符合主键))

单列主键：

代码语言：javascript复制
============单列做主键===============
#方法一：not null unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.01 sec)

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name

mysql> desc department3;
 --------- -------------- ------ ----- --------- ------- 
| Field   | Type         | Null | Key | Default | Extra |
 --------- -------------- ------ ----- --------- ------- 
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
 --------- -------------- ------ ----- --------- ------- 
rows in set (0.01 sec)

View Code

多列主键：

代码语言：javascript复制
==================多列做主键================
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
 -------------- ------------- ------ ----- --------- ------- 
| Field        | Type        | Null | Key | Default | Extra |
 -------------- ------------- ------ ----- --------- ------- 
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
 -------------- ------------- ------ ----- --------- ------- 
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'

View Code

类型四 auto_increment

设置自增字段：

代码语言：javascript复制
#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
 ------- ----------------------- ------ ----- --------- ---------------- 
| Field | Type                  | Null | Key | Default | Extra          |
 ------- ----------------------- ------ ----- --------- ---------------- 
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
 ------- ----------------------- ------ ----- --------- ---------------- 
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  1 | egon | male |
|  2 | alex | male |
 ---- ------ ------ 


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
 ---- ------ -------- 
| id | name | sex    |
 ---- ------ -------- 
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
 ---- ------ -------- 


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  8 | ysb  | male |
 ---- ------ ------ 

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  1 | egon | male |
 ---- ------ ------ 
1 row in set (0.00 sec)

View Code

设置自增字段的起始值和自增步长：

代码语言：javascript复制
#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
 ---- ------ ------ 
| id | name | sex  |
 ---- ------ ------ 
|  3 | egon | male |
 ---- ------ ------ 
row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver：自增步长
    基于表级别
    create table t1（
        id int。。。
    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）


#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 
比如：设置auto_increment_offset=3，auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
 -------------------------- ------- 
| Variable_name            | Value |
 -------------------------- ------- 
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
 -------------------------- ------- 



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
 ---- ------- ------ 
| id | name  | sex  |
 ---- ------- ------ 
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
 ---- ------- ------

View Code

类型五 foreign key

　　单独作为一个知识点来讲述 详见foreign key详解文章

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/124517.html原文链接：https://javaforall.cn

数据库 云数据库SQLServer sql 存储 unix

0 人点赞