数据类型、sql_mode补充

一.介绍

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

mysql常用数据类型概览

二.数值类型

2.1整数类型

二 基本数据类型之整型：(整型的存储宽度)
1、作用：id号，各种号码，年龄，等级
2、分类：
tinyint（**）
int （*****）
bigint（***）

3、测试：默认整型都是有符号的
create table t1(x tinyint);
insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);

create table t3(x int unsigned);#unsigied无符号
#4294967295
insert into t3 values(4294967296);

create table t4(x int(12) unsigned);
insert into t4 values(4294967296123);

4、强调：对于整型来说，数据类型后的宽度并不是存储限制，而是显示限制
    所以在创建表示，如果字段采用的是整型类型，完全无需指定显示宽度，
    默认的显示宽度，足够显示完整当初存放的数据

# 显示时，不够8位用0填充，如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);
insert into t5 values(1);

2.2浮点类型

作用：存储身高、体重、薪资
分类：
float （*****）
double （**）
decimal （**）

测试：
create table t9(x float(5,3)); #5代表最多有5位,3代表小数位占3位,整数占为2位
#相同点
#1、对于三者来说，都能存放30位小数，
#不同点：
1、精度的排序从低到高：float,double,decimal
2、float与double类型能存放的整数位比decimal更多

create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);

mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t11;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

#一般float就足够了

三.字符类型

1、作用：姓名，地址，描述类的信息

2、分类：
char 定长
varchar 变长

3、测试:字符的宽度限制单位是字符个数    #除了整型是显示限制,其他的所有宽度都是存储限制
create table t12(x char(4)); # 超出4个字符则报错，不够4个字符则用空格补全成4个字符
create table t13(y varchar(4));# 超出4个字符则报错，不够4个字符那么字符有几个就存几个

#zerofill只能填充数字

insert into t12 values('hello');
insert into t13 values('hello');

insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; #填充字符到完整的长度
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# 注意：
针对char类型，mysql在存储时会将数据用空格补全存放到硬盘中
但会在读出结果时自动去掉末尾的空格，因为末尾的空格在以下场景中是无用
mysql> select * from t14 where name="lxx"; # name字段明确地等于一个值，该值后填充空格是没有用

mysql> select * from t14 where name like "lxx"; # name字段模糊匹配一个值，该值后填充空格是有用的

# 对比char与varchar

name char(5)
# 缺点：浪费空间
# 优点：存取速度都快
egon alex lxx  wxx  yx   #egon补1个空格,alex补1个空格,wxx补2个空格 5个字符

name varchar(5)
# 缺点：存取速度都慢
# 优点：节省空间
(1bytes+egon)(1bytes+alex)(1bytes+lxx) #有报头机制，原来存lxx要5个字符(bytes)去存

#推荐使用char,因为现在硬件比较便宜

四.日期类型

1、作用：时间相关

2、分类：
date：1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999

3、测试
create table student(
    id int,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime  
);

insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');

#所以我们一般用datetime比较方便

五.集合与枚举类型

作用与分类：
枚举enum，多选一个
集合set，多选多

测试

create table teacher(
    id int,
    name char(16),
    sex enum('male','female','others'),
    hobbies set('play','read','music','piao')
);

六. sql_mode的补充

数据库里面有默认的一堆全局变量的配置,这些控制了数据库的增删改查操作
%代表任意长度任意字符，代表查看中间包含mode的变量
# 查看sql_mode
mysql> show variables like "%mode%";
+----------------------------+------------------------+
| Variable_name              | Value                  |
+----------------------------+------------------------+
| binlogging_impossible_mode | IGNORE_ERROR           |
| block_encryption_mode      | aes-128-ecb            |
| gtid_mode                  | OFF                    |
| innodb_autoinc_lock_mode   | 1                      |
| innodb_strict_mode         | OFF                    |
| pseudo_slave_mode          | OFF                    |
| slave_exec_mode            | STRICT                 |
| sql_mode                   | NO_ENGINE_SUBSTITUTION |#这是非严格模式
+----------------------------+------------------------+
8 rows in set (0.00 sec)

exit#退出mysql 
=========================================================
#修改sql_mode为严格模式：在该模式下，如果插入的数据超过限制，则会立即报错
mysql> set global sql_mode="strict_trans_tables";

mysql> show variables like "%mode%";
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes-128-ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   | 1                   |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |#这是严格模式
+----------------------------+---------------------+
8 rows in set (0.00 sec)

总结:以后尽量使用严格模式,能在应用程序解决的在应用程序解决,
虽然写程序麻烦但是扩展程序方便了,遇到扩展程序不用求人
数据库本着数据这一块的活自己揽下来,