MySQL 类型

基本类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

字符串

字符串类型：char、varchar、binary、varbinary、blob、text、enum、set。

参考
官网：The CHAR and VARCHAR Types

类型	大小	用途
char	0-255 字节	定长字符串
varchar	0-6 5535 字节	变长字符串
tinyBlob	0-255 字节	不超过 255 个字符的二进制字符串
tinyText	0-255 字节	短文本字符串
blob	0-6 5535 字节	二进制形式的长文本数据
text	0-6 5535 字节	长文本数据
mediumBlob	0-1677 7215 字节	二进制形式的中等长度文本数据
mediumText	0-1677 7215 字节	中等长度文本数据
longBlob	0-42 9496 7295 字节	二进制形式的极大文本数据
longText	0-42 9496 7295 字节	极大文本数据

1、char(n) 固定长度，无论实际使用几个字符，都将占用n个字节（存储时，实际字符数小于n时右边以空格补足，查询之时再将空格去掉）。所以char类型存储的字符串末尾不能有空格。
2、varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，text是实际字符数+2个字节。
3、text类型不能有默认值。varchar可直接创建索引，text创建索引要指定前多少个字符。

长度

-- length()：返回字符串所占的字节数(受编码影响)
-- char_length()：返回字符串中的字符数
select length('中国'), char_length('中国'); -- 6、2

字节与字符

字符与字节它们完全不是一个位面的概念，所以两者之间没有“区别”这一说法。在不同编码里，字符和字节的对应关系是不同的。
字节：1个字节（Byte）等于8个bit位，每个bit位是0/1两种状态，也就是说一个字节可以表示256个状态，计算机里用字节来作为最基本的存储单位。
字符：在计算机和电信技术中，一个字符是一个单位的字形、类字形单位或符号的基本信息。字符是指计算机中使用的字母、数字、字和符号，包括：1、2、3、A、B、C、~！·#￥%……—*（）——+等等。
①ASCII码中，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间。
②UTF-8编码中，一个英文字符等于一个字节，一个中文（含繁体）等于三个字节。
③Unicode编码中，一个英文等于两个字节，一个中文（含繁体）等于两个字节。
文本符号：英文标点占一个字节，中文标点占两个字节。
④UTF-16编码中，一个英文字母字符或一个汉字字符存储都需要2个字节（Unicode扩展区的一些汉字存储需要4个字节）。
⑤UTF-32编码中，世界上任何字符的存储都需要4个字节。
⑥GBK编码中，一个英文字符等于一个字节，一个中文（含繁体）等于两个字节。
注：GB2312 是对 ASCII 的中文扩展，而GBK 包括了 GB2312 的所有内容（GBK 比 GBK2312 多出近20000个新的汉字，包括繁体字和符号）。

varchar（50）和varchar（100）是否一样

硬盘存储需求一样，内存需求不一样。即对于VARCHAR数据类型来说，硬盘上的存储空间是根据实际字符长度来分配存储空间的，而对于内存来说，是使用N个字符空间的内存块来保存值，即varchar（50）占用50个字符内存空间，varchar（100）占用100个字符内存空间。

数值

数值类型：tinyInt、smallInt、mediumInt、int、bigInt、float、double。

类型	大小	范围（有符号）	范围（无符号）	用途
tinyInt	1 字节	(-128，127)	(0，255)	小整数值
smallInt	2 字节	(-32 768，32 767)	(0，65 535)	大整数值
mediumInt	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
int或integer	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
bigInt	8 字节	…	…	极大整数值
float	4 字节	…	…	单精度浮点数值
double	8 字节	…	…	双精度浮点数值
decimal	8 字节	…	…	双精度浮点数值

1、为了避免数据库被过度设计，布尔、枚举类型常常采用tinyint。

日期/时间

日期和时间类型：datetime、date、timestamp、time和year。

类型	大小	范围	格式	用途
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-DD HH:MM:SS	混合日期和时间值
timestamp	4 字节	1970-01-01 00:00:00/2038	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

timestamp：结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07。

类型转换

CAST(value AS TYPE); 
CONVERT(value, TYPE);

可以转换的类型是有限的，应是以下值其中的一个：

• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

// 比如字符串转数字（三种方式）：
// 方法一：ORDER BY '123' + 0;（首推）
// 方法二：ORDER BY CAST('123' AS SIGNED);
// 方法三：ORDER BY CONVERT('123', SIGNED);

约束类型

约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。
MYSQL中，常用的几种约束：

• 主键：primary key
• 外键：foreign key
• 唯一：unique
• 非空：not null
• 自增：auto_increment
• 默认值 ：default

主键约束 primary key

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

-- 基本模式
create table temp( 
    id int primary key,
    name varchar(20)
);

-- 组合模式
create table temp(
    id int ,
    name varchar(20),
    pwd varchar(20),
    primary key(id, name)
);

-- 删除主键约束
alter table temp drop primary key;

-- 添加主键约束
alter table temp add primary key(id,name);

-- 修改主键约束
alter  table temp modify id int primary key；

外键约束 foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

-- 基本模式
-- 主表
create table temp(
id int primary key,
name varchar(20)
);

-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);

-- 多列外键组合，必须用表级别约束语法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);

-- 副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/*表级别联合外键*/
foreign key(classes_name, classes_number) references classes(name, number) 
);

-- 删除外键约束
alter table student drop foreign key student_id;

-- 增加外键约束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);

唯一约束 unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。

-- 创建表时设置，表示用户名、密码不能重复
    create table temp(
    id int not null ,
    name varchar(20),
    password varchar(10),
    unique(name,password)
);


-- 添加唯一约束
alter table temp add unique (name, password);


-- 修改唯一约束
alter table temp modify name varchar(25) unique;

-- 删除约束
alter table temp drop index name;

非空约束 not null 与 默认值 default

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：
　　所有的类型的值都可以是null，包括int、float 等数据类型

-- 创建table表，ID 为非空约束，name 为非空约束 且默认值为abc
create table temp(
           id int not null,
           name varchar(255) not null default  'abc',
           sex char null
)；


-- 增加非空约束
alter table temp
modify sex varchar(2) not null;

-- 取消非空约束
alter table temp modify sex varchar(2) null;

-- 取消非空约束，增加默认值
alter table temp modify sex varchar(2) default 'abc' null;