mysql数据类型

数据类型

1）数据类型

• 整数型
• 浮点型
• 定点数
• 字符串
• 日期类型
• 修饰符

说明；

数据库的数据类型： 指每一个字段能够接受的 数据的存储格式，也就是 数据类型。

建立一个数据表时，需要制定 表中各个字段的 属性。

选择正确的数据类型对于获得高性能至关重要，三大原则：

\1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

\2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

\3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

范例

Field	字段名称
Type	字段类型
Null	是否允许为空值
Key		主键约束
Default	默认值
Extra	索引

mysql> desc test;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id     | int(11)    | YES  |     | NULL    |       |
| name   | varchar(5) | YES  |     | NULL    |       |
| age    | char(3)    | YES  |     | NULL    |       |
| gender | char(1)    | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+

1、整数类型

• tinyint(m) 占1个字节 范围(-128~127) (m) 代表宽度

• smallint(m) 2个字节 范围(-32768~32767) (m) 代表宽度

• mediumint(m) 3个字节 范围(-8388608~8388607) (m) 代表宽度

• int(m) 4个字节 范围(-2147483648~2147483647) (m) 代表宽度

• bigint(m) 8个字节 范围(±9.22*10的18次方)(m) 代表宽度

常用类型 int

范例

#整数类型测试：tinyint,int   作用：用于存储用户的年龄、游戏的Level、经验值等。
mysql> create table test1( tinyint_test tinyint , int_test int );

mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES  |     | NULL    |       |                    ## 不指定宽度的话，系统会自动设置一个合理的值，确保 int 能够显示完整。
| int_test     | int(11)    | YES  |     | NULL    |       | 
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)

mysql> insert into test1 (tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1

//测试结果，默认有符号，超过存储范围出错。


#====无符号整形测试 unsigned========

mysql> create table test2(
    -> tinyint_test tinyint unsigned,		    //约束条件unsigned限定只能存正值（无符号）
    -> int_test int unsigned
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES  |     | NULL    |       | 
| int_test     | int(10) unsigned    | YES  |     | NULL    |       | 
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)

mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column   'tinyint_test' at row 1

整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度，由系统自行调整。

2、浮点类型

• float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
• double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位
• deecimal(m,d) 定点数

1、float 浮点数

说明：

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以
实际为准，即6位

用于存储用户的身高、体重、薪水等，不具备超高精度要求的数据
浮点数是用来表示实数的一种方法，它用 M(尾数) * B( 基数)的E(指数）次方来表示实数，相对于定点数来说，
在长度一定的情况下，具有表示数据范围大的特点。
但同时也存在误差问题。如果希望保证值比较准确，推荐使用定点数数据类型。

范例：

#优先满足小数位，少补，多 四舍五入
mysql> create table test4(float_test float(5,2));  //一共5位，小数占2位 000.00 --  999.99
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field      | Type       | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES  |     | NULL    |       | 
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test4  values (10.2 ), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test4;
+------------+
| float_test |
+------------+
|      10.20 | 
|      70.24 | 
|      70.25 | 
+------------+
3 rows in set (0.00 sec)

mysql> insert into test4 values (1111.20);        ### 优先满足小数位，少补，多 四舍五入
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1

2、decimal(m,d) 定点数

说明：

定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

M的取值范围为1~65，取0时会被设为默认值，超出范围会报错。
D的取值范围为0~30，而且必须<=M，超出范围会报错。

范例：

#定点数decimal测试：
mysql>  create table test5(decimal_test  decimal(5,2));

mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> show  warnings;           ##显示系统的警告信息。
+-------+------+---------------------------------------------------+
| Level | Code | Message                                           |
+-------+------+---------------------------------------------------+
| Note  | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test5;
+-------+
| num   |
+-------+
| 70.25 |
| 70.25 |
+-------+

总结;

定点数 decimal (M,D) ：不存在精度偏差。 取值 65 小数最大支持 30位，

浮点数：在一定范围内能保证精确，超出的范围，可能会出现存取的偏差。

对小数部分的处理机制一样： 优先处理小数部分内容（不够小数位，则补0 ，超出的小数位，则按照四舍五入自动截断。

3、字符串类型

• char(n) 固定长度，最多255个字符,注意不是字节
• varchar(n) 可变长度，最多65535个字符
• enum() 枚举类型
• set() 集合类型

1、char、varchar 类型

说明：

CHAR 列的长度固定值，为创建表时声明的长度： 0 ~ 255
VARCHAR 列中的值为可变长字符串，长度： 0 ~ 65535

varchar 可以使用 多个 字节用于 记录 长度信息，所以当存储长度超过 255 时， 会使用 2 个字节来记录总长度信息，因此可以支持高达 65535 的长度 。

作用：用于存储用户的姓名、爱好、发布的文章等

**注：**在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格

范例：

#创建一个表姓名允许多个字符串，电话只允许写11位字符串
mysql> create table test1 (姓名 varchar(5),电话 char(11));

mysql> desc test1;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| 姓名   | varchar(5) | YES  |     | NULL    |       |
| 电话   | char(11)   | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+

2、enum、set类型

说明：

enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3…）

范例：

#测试
表     school.student3											                
姓名	name	varchar(50)									            
性别	sex		enum('m','f')					                    
爱好	hobby	set('music','book','game','disc')	                

mysql> use school
mysql> create table student3(
    -> name varchar(50),
    -> sex  enum('m','f'),
    -> hobby  set('music','book','game','disc')
    -> );
Query OK, 0 rows affected (0.31 sec)

mysql> desc student3;
+-------+-----------------------------------+------+-----+---------+-------+
| Field | Type                               | Null | Key | Default | Extra|
+-------+-----------------------------------+------+-----+---------+-------+
| name  | varchar(50)                        | YES  |     | NULL    |      |
| sex   | enum('m','f')                      | YES  |     | NULL    |      |
| hobby | set('music','book','game','disc')  | YES  |     | NULL    |      |
+-------+-----------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into student3 values ('tom','m','book,game');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student3;
+------+------+-----------+
| name | sex  | hobby     |
+------+------+-----------+
| tom  | boy  | book,game | 
+------+------+-----------+
1 row in set (0.00 sec)

#错误范例
mysql>  insert into student3 values ('jack','m','film');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

4、日期时间型

• date 年月日 ‘2008-12-2’
• datetime 年月日时分秒’2008-12-2 22:06:44’
• time 时分秒
• year 年份
• timestamp 自动存储记录修改时间

说明：

timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记

录最后被修改的时间

作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

范例：

#时间和日期类型测试：year、date、time、datetime、timestamp

mysql> create table test_time(d date,t time,dt datetime);

Query OK, 0 rows affected (0.03 sec)

mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
 
 #调用 now() 函数，取得当前时间，并插入列值。
mysql> insert into test_time values(now(),now(),now());  

mysql> select * from test_time;
+------------+----------+------------------------+
| d              | t           | dt                         |
+------------+----------+------------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

#默认设置当前时间
mysql> create table t(id timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> desc t;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> insert into t values(null);       "null"   null
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+--------------------------+
| id                             |
+--------------------------+
| 2013-12-18 00:08:41 |
+--------------------------+
1 row in set (0.00 sec)


#使用 当前 时间 作为 字段 默认值， 注意，只有 timestamp 类型支持设置默认 
ts_time timestamp NOT NULL DEFAULT NOW();
ts_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(); ##默认值设置为 当前时间 是业务上一种很常见的用法。

#常见的日期获取函数
MySQL中有一些日期函数可供我们使用，我们可以使用 ” select 函数名() ; ” 的 sql 查看它们的返回值。
同时也可以使用 “select 自定义函数名();”，调用我们自己定义的函数。 
Eample:

select current_timestamp();
select current_date();
select current_time();
select now();

5、约束条件

• 非空约束 ：null（默认允许为空值）、not null（表示这个字段不能是空，空格不算空值）
• 唯一约束：unique
• 主键约束：primary key(字段)
• 联合主键：primary key(字段1,字段2)
• 外键约束：foreign key

1、非空约束 null、not null

说明：

null：表示可以为空值

not null：表示这个字段不能是空值

表中，所有记录里面，尽量不要出现 “空值” ，大量空值会影响表的 读取和索引效率。

范例

#非空值测试
mysql> create table test_1(
    -> id int not null,
    -> name varchar(5));

mysql> desc test_1;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   |     | NULL    |       |
| name  | varchar(5) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

mysql> insert into test_1 values (1,'张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

#错误范例
mysql> insert into test_1(name) values ('李四');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

2、唯一约束 unique key

说明：

添加唯一约束，则该列的值不能出现重复值，而且唯一键的列，允许有 空值 ， 主键不允许出现空值。
允许出现多个唯一键。

范例：

 ID       用户名      邮箱
 主键     唯一键      唯一键  

#唯一约束测试
mysql> create table 唯一约束(id int unique key,name varchar(10));

mysql> desc 唯一约束;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Nusll | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | UNI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into 唯一约束 values (1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into 唯一约束 values (1,'王五');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

#删除唯一约束
写法：alter table 表格名称 drop index 字段名
mysql> alter table 唯一约束 drop index name;

3、主键约束 primary key

说明：

主键约束相当于 (唯一约束+非空约束)

主键可以保证记录的唯一，主键域自动为NOT NULL(非空);
主键常常与外键构成参照完整性约束，防止出现数据不一致。
数据库管理系统对于主键自动生成唯一索引，所以主键也是一个特殊的索引。

写法：

create table 表格名称(字段1 字典类型1,字段2 字段类型2,primary key(字段));

范例：

mysql> create table 主键约束(id int,name varchar(20),grade double,primary key(id));

mysql> desc 主键约束;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | double      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

#删除主键，因为主键也属于表结构的一部分，所以对主键操作就是操作表结构； 
mysql> alter table 主键约束 drop primary key;

4、联合主键

说明；

选定的两个字段中不能出现一样的，两个不完全一样

写法

create table 表格名称(
	字段1 字典类型1,
	字段2 字段类型2,
	primary key(字段1,字段2)
	);

范例；

#創建联合主键

mysql> create table 联合主键(id int,name varchar(4),age int,sex char(1),primary key(id,name));
Query OK, 0 rows affected (0.02 sec)

mysql> desc 联合主键;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   | PRI | NULL    |       |
| name  | varchar(4) | NO   | PRI | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
| sex   | char(1)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

总结；

主键约束 vs 联合主键

一张表中最多有一个主键约束（单独字段，联合字段），如果设置多个主键,就会出现如下提示： Multiple primary key defined !!!
删除主键约束前，如果有自增长属性 ，则 需要先删除自增长属性 , 如果不删除自增长就无法删除主键约束

5、外键约束 foreign key

说明：

一张表对另外一张表中设置一个外键，可以关联在一起，同步数据

主表对应的字段只能是 主键 或者 唯一约束修饰的字段

写法：

constraint fk_外键名称 foreignkey（从表_id）references 主表 (主表_id)

范例：

#先创建两张单独的表，在对表约束进行修改，添加外键约束

# 主键表 ======================
mysql> create table class(
    -> cla_id int(10) auto_increment primary key,
    -> cla_name varchar(30) not null unique
    -> );

#插入一些数据
mysql> insert into class values (1,'主机');
+--------+----------+
| cla_id | cla_name |
+--------+----------+
|      4 | java     |
|      1 | 主机     |
|      3 | 开发     |
|      2 | 运维     |
+--------+----------+
mysql> desc class;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| cla_id   | int(10)     | NO   | PRI | NULL    | auto_increment |
| cla_name | varchar(30) | NO   | UNI | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

# 外键表 ======================
mysql> CREATE TABLE sutlass(
    -> stu_id INT(10) AUTO_INCREMENT PRIMARY KEY,
    -> stu_name CHAR(10) NOT NULL,
    -> classid TINYINT
	-> );

mysql> insert into sutlass values (1,'张三',1);
mysql> insert into sutlass values (2,'李四',1);
mysql> insert into sutlass values (3,'老王',2);
mysql> insert into sutlass values (4,'小明',4);
+--------+----------+---------+
| stu_id | stu_name | classid |
+--------+----------+---------+
|      1 | 张三     |       1 |
|      2 | 李四     |       1 |
|      3 | 老王     |       2 |
|      4 | 小明     |       4 |
+--------+----------+---------+

mysql> desc sutlass;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| stu_id   | int(10)  | NO   | PRI | NULL    | auto_increment |
| stu_name | char(10) | NO   |     | NULL    |                |
| classid  | int(11)  | YES  | MUL | NULL    |                |
+----------+----------+------+-----+---------+----------------+

#创建表后使用alteer添加外键
mysql> ALTER TABLE sutlass ADD CONSTRAINT fk_classid FOREIGN KEY(classid) REFERENCES class(cla_id) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.04 sec)

#删除外键
mysql> alter table sutlass drop foreign key fk_classid;
Query OK, 0 rows affected (0.01 sec)

错误提示：
mysql> ALTER TABLE sutlass ADD CONSTRAINT fk_classid FOREIGN KEY(classid) REFERENCES class(cla_id);
ERROR 1215 (HY000): Cannot add foreign key constraint
原因在是由于主外键之间的数据类型不一致造成的，以后类似问题，皆可按此处理。

6、自增约束 auto_increment

说明：

用于主键并且是一个字段的主键，才能使用auto_increment，默认从1开始
可以使字段从数字1开始的数逐一递增的，由系统自行管理，不需要人工干涉，可以保证 唯一性 ；

一张表只能有一个自增长列，并且该列必须定义了约束 (可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束)
不过自增长一般配合主键使用，并且只能在数字类型中使用

写法：

create table 表格名称(
	字段 字段类型 auto_increment primary key,
	);

7、默认约束 default

说明：

采用default关键字，在该字段没有赋值的时候设置默认值；
如果一个字段没有默认值就为 null
默认值存在一些特殊的关键字 default，也可以在值 中使用 函数

写法：

create table 表格名称(
	字段 字段类型 deefault 默认值,
	);

范例：

mysql> create table t12(id int,timee timestamp not null default now());