mysql数据类型
一、引擎
前提: 引擎是建表是规定, 它是提供给表使用的, 而不是数据库。
mysql> show engines; # 展示所有引擎 》》》》结果： ----------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------- ----------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for tempo ary tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and oreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------- # 重点: # innodb(默认): 支持事务, 行级锁, 外键 # myisam: 查询效率要优于innodb, 当不需要支持事务, 行级锁, 外键, 可以通过设置myisam来优化数据库 mysql> use db1; ##创建各种类型引擎的表： mysql> create table t1(id int)engine=innodb; mysql> create table t2(id int)engine=myisam; mysql> create table t3(id int)engine=blackhole; #写入的数据马上消失 mysql> create table t4(id int)engine=memory;#数据只存在内存 insert into t1 values(1); insert into t2 values(1); insert into t3 values(1); insert into t4 values(1); select * from t1; ...
二、创建表的完整的语法
''' create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名2 类型[(宽度) 约束条件], 字段名3 类型[(宽度) 约束条件] )engine=innodb charset=utf8; #默认引擎为innodb，设置了配置文件后的默认编码为utf8。 ''' # []可选参数 # create table db1.t1(name char(3) not null); [(3)宽度 not null约束条件] # 数据插入时,name不能为空(null), 且最长只能存放三个字符 # 总结: 宽度和约束条件为可选参数, 用来限制存放数据的规则；字段名和类型是必须的；在同一张表中，字段名是不能相同。 ###创建表 mysql> create table t1( -> id int, -> name varchar(50), -> sex enum('male','female'), -> age int(3)); Query OK, 0 rows affected (0.60 sec) mysql> show tables; +---------------+ | Tables_in_db1 | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | age | int(3) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 4 rows in set (0.03 sec)
三、数据库的模式
# sql_mode: 反映数据库的全局变量 # 数据库模式限制的是客户端对服务器操作数据的方式(是否严格) # 两种模式 no_engine_substitution：非安全性，默认 #非数据安全，即在限定字段长度的情况下后，存入该字段的数据超出长度后，仍能截断存入不发生报错，造成数据不全。 strict_trans_tables：安全模式 #数据安全，即在限定字段长度的情况下后，存入该字段的数据超出长度后，就会报错。 # 查看当前数据库模式: show variables like "%sql_mode%"; # %匹配0~n个任意字符 => 模糊查询 # 设置为安全模式 set global sql_mode="strict_trans_tables"; # 重启连接(客户端) quit # 应用 create table t1(name char(2)); #表示name字段下的字符长度为2个字符。 insert into t1 values ("ab") # 正常 insert into t1 values ("zero") # 错误 Data too long for column 'name' at row 1
四、数据类型
mysql数据库支持存放的数据：整型、浮点型、字符型、时间类型、枚举型、集合类型
1、整型
'''类型 任意类型下都分为有符号型和无符号型，类型本身不带符号，能够区分是用字节首位进行标识。 tinyint：1字节（8位二进制） signed -128~127 * unsigned 0~255 smallint：2字节 mediumint：3字节 int：4字节 signed -2147483648~2147483647 * unsigned 0～4294967295 bigint：8字节 ''' '''约束 * unsigned：无符号 zerofill：0填充 ''' # 不同类型所占字节数不一样, 决定所占空间及存放数据的大小限制 # eg: create table t8(x tinyint); # 默认为signed insert into t8 values(200); # 非安全模式存入,值只能到最大值127 select (x) from t8; '''宽度 1.不能决定整型存放数据的宽度, 超过宽度可以存放, 最终由数据类型所占字节决定 2.如果没有超过宽度,且有zerofill限制, 会用0填充前置位的不足位 3.没有必要规定整型的宽度, 默认设置的宽度就为该整型能存放数据的最大宽度 * ''' # eg:1 create table t9(x int(5)); #定义的宽度5为显示宽度，一般使用系统默认的 insert into t9 values(123456); select (x) from t9; # 结果: 123456 insert into t9 values(2147483648); select (x) from t9; # 结果: 2147483647 insert into t9 values(10); select (x) from t9; # 结果: 10 # eg:2 create table t10(x int(5) unsigned zerofill); # 区域0~4294967295 insert into t10 values(10); select x from t10; # 结果: 00010 insert into t10 values(12345678900); select x from t10; # 结果: 4294967295
注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下 其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了 默认的显示宽度，都是在最大值的基础上加1
2、浮点型
'''类型 float：4字节，3.4E–38～3.4E+38 * double：8字节，1.7E–308～1.7E+308 decimal：M，D大值基础上+2 ''' '''宽度： 限制存储宽度 (M, D) => M为位数，D为小数位 float(255, 30)：精度最低，最常用 double(255, 30)：精度高，占位多 decimal(65, 30)：字符串存，全精度 ''' # eg:1 create table t11 (age float(256, 30)); # Display width out of range for column 'age' (max = 255) create table t11 (age float(255, 31)); # Too big scale 31 specified for column 'age'. Maximum is 30. # eg:2 create table t12 (x float(255, 30)); create table t13 (x double(255, 30)); create table t14 (x decimal(65, 30)); insert into t12 values(1.11111111111111111111); insert into t13 values(1.11111111111111111111); insert into t14 values(1.11111111111111111111); select * from t12; # 1.111111164093017600000000000000 => 小数据,精度要求不高, 均采用float来存储 * select * from t13; # 1.111111111111111200000000000000 select * from t14; # 1.111111111111111111110000000000 alter table t14 modify x decimal(10, 5); # 1.11111 => 限制了数据的存储宽度
3、字符型
'''类型 char：定长 varchar：不定长 ''' '''宽度 限制存储宽度 char(4)：以4个字符存储定长存储数据 字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节） 存储： 存储char类型的值时，会往右填充空格来满足长度 例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储 检索： 在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';） varchar(4)：数据长度决定字符长度，为可变长度存储数据 字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8） 存储： varchar类型存储数据的真实内容，不会用空格填充，如果'ab ',尾部的空格也会被存起来 强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用） 如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255） 如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535） 检索： 尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容 ''' # eg: create table t15 (x char(4), y varchar(4)); insert into t15 values("zero", 'owen'); # '' | "" 均可以表示字符 select x,y from t15; # 正常 insert into t15 values("yanghuhu", 'lxxVSegon'); # 非安全模式数据丢失,可以存放, 安全模式报错 select x,y from t15; # 可以正常显示丢失后(不完整)的数据 insert into t15 values('a', 'b'); # 验证数据所在字符长度 # 前提: 安全模式下以空白填充字符 set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"; # 重启连接 select char_length(x), char_length(y) from t15; # a占4 b占1 '''重点: 存储数据的方式 ** => 数据库优化 char: 一定按规定的宽度存放数据, 以规定宽度读取数据, 通常更占空间 varchar: 首先根据数据长度计算所需宽度, 并在数据开始以数据头方式将宽度信息保存起来, 是一个计算耗时过程, 取先读取宽度信息,以宽度信息为依准读取数据, 通常节省空间 ''' 8: zero egon lxx yanghuhu 8: 4zero4egon3lxx8yanghuhu 注: varchar的数据头占1~2字节 规定char|varchar宽度均为4,用来存放4个字符的数据, char存取更高效,char占4字符,varchar占5字符,char更省空间 总结: 数据长度相近的数据提倡用char来存放数据, 数据需要高速存取,以空间换时间, 采用char
4、时间类型
'''类型 year：yyyy(1901/2155) date：yyyy-MM-dd(1000-01-01/9999-12-31) time：HH:mm:ss datetime：yyyy-MM-dd HH:mm:ss(1000-01-01 00:00:00/9999-12-31 23:59:59) timestamp：yyyy-MM-dd HH:mm:ss(1970-01-01 00:00:00/2038-01-19 ??) ''' # eg: 1 create table t16(my_year year, my_date date, my_time time); insert into t16 values(); # 三个时间类型的默认值均是null insert into t16 values(2156, null, null); # 在时间范围外,不允许插入该数据 insert into t16 values(1, '2000-01-01 12:00:00', null); # 2001 2000-01-01 null insert into t16 values(2019, '2019-01-08', "15-19-30"); # time报格式错误 => 按照时间规定格式存放数据 alter table t16 change my_year myYear year(2); # 时间的宽度修改后还是采用默认宽度 => 不需要关系宽度 # eg:2 create table t17(my_datetime datetime, my_timestamp timestamp); insert into t17 values(null, null); # 可以为空, 不能为null,赋值null采用默认值current_timestamp insert into t17 values('4000-01-01 12:00:00', '2000-01-01 12:00:00'); # 在各自范围内可以插入对应格式的时间数据 # datetime VS timestamp datetime：时间范围，不依赖当前时区，8字节，可以为null timestamp：时间范围，依赖当前时区，4字节，有默认值CURRENT_TIMESTAMP
5、枚举与集合
'''类型 enum：单选 set：多选 ''' create table t19( sex enum('male','female','wasai') not null default 'wasai', # 枚举 hobbies set('play','read','music') # 集合 ); insert into t19 values (null, null); # sex不能设置null insert into t19 values (); # wasai null insert into t19 (hobbies) values ('play,read'), ('music,play'); # sex采用默认值, 对hobbies字段添加两条记录 insert into t19 (sex,hobbies) values ('male,female', 'play'); # sex字段只能单选
五、约束条件
""" primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段 foreing key：外键 unique key：唯一性数据, 该条字段的值需要保证唯一,不能重复 auto_increment：自增，只能加给key字段辅助修饰 not null：不为空 default：默认值 unsigned：无符号 zerofill：0填充 """ 注： 1.键是用来讲的io提供存取效率 2.联合唯一 create table web ( ip char(16), port int, unique(ip,port) ); 3.联合主键 create table web ( ip char(16), port int, primary key(ip,port) ); # eg:1 # 单列唯一 create table t20 ( id int unique ); # 联合唯一 create table web ( ip char(16), port int, unique(ip,port) ); # 如果联合两个字段,两个字段全相同才相同,否则为不同 insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306); # 注: # 1.表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键) # 2.没有设置主键的表, 数据库系统会自上而下将第一个规定为unique not null字段自动提升为primary key主键 mysql> create table t6(age int not null unique); Query OK, 0 rows affected (0.23 sec) mysql> desc t6; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | age | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> create table t7(age int unique); Query OK, 0 rows affected (0.36 sec) mysql> desc t7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | age | int(11) | YES | UNI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) # 3.如果整个表都没有unique not null字段且没有primary key字段, 系统会默认创建一个隐藏字段作为主键 # 4.通常必须手动指定表的主键, 一般用id字段, 且id字段一般类型为int, 因为int类型可以auto_increment # eg:2 create table t21(id int auto_increment); # 自增约束必须添加给key的字段 # eg:3 create table t21(id int primary key auto_increment); # 自增要结合key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留 # eg:4 # 联合主键 create table t22( ip char(16), port int, primary key(ip,port) ); # 如果联合两个字段,两个字段全相同才相同,否则为不同 insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);
单列做主键
==================多列做主键================ create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(ip,port) ); mysql> desc service; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | NULL | | | port | char(5) | NO | PRI | NULL | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> insert into service values -> ('172.16.45.10','3306','mysqld'), -> ('172.16.45.11','3306','mariadb') -> ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into service values ('172.16.45.10','3306','nginx'); ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
