修改MySQL所有表的id类型_MYSQL——修改表，表字段类型及约束条件

#1、=====================表字段类型之整型=======================

强调：整型的宽度是显示宽度，无需设置，存储宽度是固定死的

mysql>create table t5(id tinyint)

mysql>desc t4;+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | tinyint(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.01sec)

mysql> insert t4 values(128);

ERROR1264 (22003): Out of range value for column 'id' at row 1mysql>mysql>mysql> insert t4 values(127);

Query OK,1 row affected (0.05sec)

mysql> select * fromt4;+------+

| id |

+------+

| 127 |

+------+

1 row in set (0.00sec)

mysql>

#2、=====================表字段类型之浮点类型=======================

create table t7(x float(255,30),y double(255,30),z decimal(65,30));

insert t7 values

(1.111111111111111111111111111111,1.111111111111111111111111111111,1.111111111111111111111111111111);#3、=====================表字段类型之日期类型======================

year(1901/2155)

time 时:分:秒 ('-838:59:59'/'838:59:59')

date 年:月:日 (1000-01-01/9999-12-31)

datetime 年:月:日 时:分:秒1000-01-01 00:00:00/9999-12-31 23:59:59timestamp 年:月:日 时:分:秒1970-01-01 00:00:00/2037create table t8(y year,t time,d date,dt datetime,ts timestamp);

insert t8 values(now(),now(),now(),now(),now());

create table student(

id int,

name char(10),

born_year year,

bitrh date,

reg_time datetime

);

insert student values

(1,"wangjing","1911","1911-11-11","1911-11-11 11:11:11"),

(2,"lxx","1988","1988-11-11","1988-11-11 11:11:11");

insert student values

(3,"wangjing","1911","19111111","19111111111111");#注意：timestamp应该勇于记录更新时间

create table t9(

id int,

name varchar(16),-- update_time datetime notnull default now() on update now(),

update_time timestamp,

reg_time datetimenotnull default now()

);

insert into t9(id,name) values(1,"egon");#测试效果

mysql> select * fromt9;+------+------+---------------------+---------------------+

| id | name | update_time | reg_time |

+------+------+---------------------+---------------------+

| 1 | egon | 2020-09-01 16:45:51 | 2020-09-01 16:45:51 |

+------+------+---------------------+---------------------+

1 row in set (0.00sec)

mysql> update t9 set name="EGON" where id=1;

Query OK,1 row affected (0.06sec)

Rows matched:1 Changed: 1Warnings: 0

mysql> select * fromt9;+------+------+---------------------+---------------------+

| id | name | update_time | reg_time |

+------+------+---------------------+---------------------+

| 1 | EGON | 2020-09-01 16:46:50 | 2020-09-01 16:45:51 |

+------+------+---------------------+---------------------+

1 row in set (0.00sec)

mysql>

#4、=====================表字段类型之字符类型======================

char 定长，不够则补全空格

看起来特点：

浪费空间

读取速度快

varchar 变长，预留1-2bytes来存储真实数据的长度

看起来特点：

节省空间

读取速度慢

ps：在存储的数据量刚好达到存储宽度限制时，其实varchar更费空间

总结：大多数情况下存储的数据量都达不到宽度限制，所以大多数情况下varchar更省空间

但省空间不是关键，关键是省空间 会带来io效率的提升，进而提升了查询效率

ab|abc |abcd |1bytes+ab|1bytes+abc|1bytes+abcd|

===============验证

create table t11(x char(5));

create table t12(x varchar(5));

insert t11 values("我擦嘞"); -- "我擦嘞"insert t12 values("我擦嘞"); -- "我擦嘞"t11=>字符个数 5 字节个数 11t12=>字符个数 4 字节个数 10set sql_mode="pad_char_to_full_length";

select char_length(x)fromt11;

select char_length(x)fromt12;

select length(x)fromt11;

select length(x)fromt12;#5、=====================表字段类型之枚举类型与集合======================

枚举类型enum("a","b","c","d") 多选1

集合类型set("a","b","c","d") 多选

CREATE TABLE shirts (

name VARCHAR(40),

size ENUM('x-small', 'small', 'medium', 'large', 'x-large')

);

INSERT INTO shirts(name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

CREATE TABLE user (

name VARCHAR(16),

hobbies set("read","chou","drink","tang")

);

insert user values("lxx","tang,chou");

insert user values("hxx","tangchou");