MySQL基本数据类型

目录

• mysql中的数据类型
  • 1 整数
    • 注意：默认情况整数是有符号的
  • 2 浮点数
    • 注意：同样是默认有符号，设置无符号的方式和上面相同
  • 3 字符串
    • char(10)
    • varchar(10)
    • text
    • blob
    • 注意：字符串右端空格自动清除
  • 4 日期和时间
    • date/time/datetime 示例
    • timestamp示例
  • 5 枚举 集合
  • 设置严格/非严格模式

mysql中的数据类型

1 整数

类型	大小
tinyint	1 字节
smallint	2 字节
mediumint	3 字节
int	4 字节
bigint	8字节

1个字节就是8位，范围是有符号(-128, 127)，有符号(0, 255)，详情可见X位的有/无符号整数详细介绍

注意：默认情况整数是有符号的

设置为无符号的三种方式

• 新建表的时候：create table 表名(字节名 tinyint unsigned);
• 新建字节：alter table 表名 add 字节名 tinyint unsigned;
• 修改字节：alter table 表名 modify 字节名 tinyint unsigned;

# 测试整数默认情况下有无符号
mysql> create table t1(id tinyint);
# 严格模式就会报错，非严格模式就会保存成127，说明是有符号的
mysql> insert into t1 values(200);

# 指定长度
mysql> create table t2(id int(10) zerofill);
mysql> insert into t2 values(1);
mysql> select * from t2;
+------------+
| id         |
+------------+
| 0000000001 |
+------------+
1 row in set (0.01 sec)

# int(10) 如果你的数据不足10位，用0填充（需要添加zerofill 这个约束）
# 对于整数和小数而言，都是用来控制显示填充的最小长度

2 浮点数

类型	大小	精确程度
float	4字节	不精确（大约5位小数）
double	8字节	不精确（比float更加精确）
decimal	需要指定	精确(65, 30)

# float
mysql> create table t3(id float);
mysql> insert into t3 values(1.2222222222);
mysql> select * from t3;
+---------+
| id      |
+---------+
| 1.22222 |
+---------+
1 row in set (0.00 sec)

# double
mysql> create table t4(id double);
mysql> insert into t4 values(1.22222222222222222);
mysql> select * from t4;
+--------------------+
| id                 |
+--------------------+
| 1.2222222222222223 |
+--------------------+
1 row in set (0.00 sec)

# decimal(4, 3): 一共有4位，小数部分占3位
mysql> create table t5(id decimal(4, 3));
mysql> insert into t5 values(22.22);	# 报错，整数部位多了
mysql> insert into t5 values(1.22222);	# 不报错，但是只会存1.222，后面的会被截断
Query OK, 0 rows affected (0.04 sec)

# float(10)	和int同样，补充整数位

• decimal
  • 使用方法decimal(x, y) x是总长度，y是小数点后长度
  • 范围内不会丢失精度，范围外会提醒丢失
  • 总长度最大65，小数长度最大30，超出范围就报错

注意：同样是默认有符号，设置无符号的方式和上面相同

3 字符串

类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

char(10)

• 存储'abc' 占10个字符长度
• 优点：:读取存储速度快于 varchar
• 缺点：浪费磁盘空间

varchar(10)

• 存储'abc' 占4个字符(3个长度字符外加一个bit存储字符长度)
• 优点：节省空间
• 缺点：速度慢于char类型

# char如果不规定长度，那默认是1
mysql> create table t8(id, char);
mysql> insert into t8 values('123');	# 报错

# char规定长度，即便输入的长度小于最大长度，也就保存为最大长度
# varchar规定长度，保存长度为输入长度+1
# 测不出来

# 在字符串后面加空格，mysql会自动删掉
mysql> insert into t8 values(1      , 2    );
mysql> select * from t8;

如果字符长度不大，建议使用char类型，反之则选择varchar

text

• 具备编码的大字符串

blob

• 没有编码的二进制大对象
• 可以用来储存可变数量的数据，但一般不会存，都是建一个文件服务器，然后用数据库保存文件的地址信息

注意：字符串右端空格自动清除

• mysql会自动将字符串后面的空格删掉，所以你的数据在存储前应该先把空格处理掉

4 日期和时间

类型	大小 (字节)	范围	格式	用途
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	年月日
time	3	'-838:59:59'/'838:59:59'	HH:MM:SS	时分秒
year	1	1901/2155	YYYY	年份值
datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DDHH:MM:SS	年月日时分秒
timeshamp	4	1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDDHHMMSS	混合日期和时间值，时间戳

year date time 都可以用字符串或数字两种方式输入值

上述所有的类型年份都可以是两位数，0-69翻译为2000-2069 70-99 翻译为1970-1999

# date time year datetime timestamp都可以使用字符串和整形两种方式添加记录，并且年份可以简写
# date
mysql> create table t9(y date);
mysql> insert into t9 values('09-01-01');
mysql> select * from t9;
+------------+
| y          |
+------------+
| 2009-01-01 |
+------------+
1 row in set (0.00 sec)

mysql> insert into t9 values(090101);
mysql> select * from t9;


# datetime
mysql> create table t11(dt datetime);
# 时间也可以不写，月日必须写，年可以不写，默认2000年
mysql> insert into t11 values(0101);	
mysql> insert into t11 values(120202);
mysql> insert into t11 values('13-03-03 10:10:00')
mysql> select * from t11;
+---------------------+
| dt                  |
+---------------------+
| 2000-01-01 00:00:00 |
| 2012-02-02 00:00:00 |
| 2013-03-03 10:10:00 |
+---------------------+
3 rows in set (0.00 sec)

#timestamp
mysql> create table t12(ts timestamp);
mysql> insert into t12 values(121001);
mysql> insert into t12 values('10-05-01 10:10:10');
mysql> insert into t12 values(100101123000);
mysql> select * from t12;
+---------------------+
| ts                  |
+---------------------+
| 2012-10-01 00:00:00 |
| 2010-05-01 10:10:10 |
| 2010-01-01 12:30:00 |
+---------------------+
3 rows in set (0.00 sec)

# 添加当前时间
# 时间相关的函数:now()
mysql> insert into t12 values(now());
# 时间相关的常量:CURRENT_TIME 
mysql> insert into t12 values(current_time);

date/time/datetime 示例

mysql> create table t4 (d date,t time,dt datetime);
mysql> desc t4;
+-------+----------+------+-----+---------+-------+
| 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 t4 values (now(),now(),now());
mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)

# 添加null
mysql> insert into t4 values (null,null,null);
mysql> select * from t4;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

timestamp示例

mysql> create table t5 (id1 timestamp);
mysql> desc t5;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| id1   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
row in set (0.00 sec)

# 插入数据null，会自动插入当前时间的时间
mysql> insert into t5 values (null);
mysql> select * from t5;
+---------------------+
| id1                 |
+---------------------+
| 2019-07-12 00:36:41 |
+---------------------+
1 row in set (0.00 sec)

#添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table t5 add id2 timestamp;
mysql> show create table t5 G	# 这里加;会报错
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


# 手动修改新的列默认值为当前时间
mysql> alter table t5 modify id2 timestamp default current_timestamp;
mysql> show create table t5 G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `id2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t5 values (null,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t5;
+---------------------+---------------------+
| id1                 | id2                 |
+---------------------+---------------------+
| 2019-07-12 00:36:41 | 0000-00-00 00:00:00 |
| 2019-07-12 00:41:10 | 2019-07-12 00:41:10 |
+---------------------+---------------------+
2 rows in set (0.00 sec)

5 枚举 集合

枚举是提前规定一个范围 你的值只能是其中之一 多选一

集合是提前规定一个范围 你的值可以是其中的多个 多选多

集合在插入数据时 ,多个值之前用逗号隔开 但他们必须包含在同一个字符串内!

# enum，不能插入不属于设定值的值，也只能插入一个值
mysql> create table t11(gender enum('man', 'woman'));
mysql> insert into t11 values('123');	# 报错
mysql> insert into t11 values('man, woman'); 	# 报错
mysql> insert into t11 values('man');

# set 不能插入不属于设定值的值，可以插入多个值，会去重
mysql> create table t13(hobby set('swim', 'play', 'run', 'read'));
mysql> insert into t13 values ('swim,play,swim');

设置严格/非严格模式

mysql 5.6默认是非严格模式，如果你的数据超出范围，不会报错，会自动取该数据类型的最大值，所以容易丢失数据，可以进行一些修改。

# 查看当前的sql模式
select @@sql_mode;   # @@表示系统内置变量   @表示用户自定义的

+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

# 临时设置sql_mode，退出登录后又得设置
set @@sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
					# 原有的				设置为严格模式

# 想要永久设置，直接修改配置文件即可
[mysqld]
sql_mode = NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES

• 局部变量：只对当前用户有效
• 全局变量：所有客户端都有效