14、mysql数据库基础

作者: hello大象 | 来源:发表于2017-10-24 16:36 被阅读0次

其中int(0)代表整型，宽度是0位。char(16)定长的字符串，定义的宽度为16位（表示你存储的字符将占16个字节）。varchar(16)可变长度字符串，长度是16位（字节）【一个汉字gbk2个字节，utf83个字节】。datetime保存时间日期的，text用来存储字符串类型、大文本数据。

一个非常重要的点：mysql数据库的时候有多种数字的类型，主要有int,bigint,smallint和tinyint。其中比较迷惑的是int和smallint的差别。今天就在网上仔细找了找，找到如下内容，做个总结：
使用整数数据的精确数字数据类型。
bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
P.S. bigint已经有长度了，在mysql建表中的length，只是用于显示的位数
int
从 -2^31 (-2,147,483,648) 到 2^31 – 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。
smallint
从 -2^15 (-32,768) 到 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。
tinyint
从 0 到 255 的整型数据。存储大小为 1 字节。2^8=256,一个bit记录正负、
注释
在支持整数值的地方支持 bigint 数据类型。但是，bigint 用于某些特殊的情况，当整数值超过 int 数据类型支持的范围时，就可以采用 bigint。在 SQL Server 中，int 数据类型是主要的整数数据类型。
在数据类型优先次序表中，bigint 位于 smallint 和 int 之间。
只有当参数表达式是 bigint 数据类型时，函数才返回 bigint。SQL Server 不会自动将其它整数数据类型（tinyint、smallint 和 int）提升为 bigint。
int(M) 在 integer 数据类型中，M 表示最大显示宽度。在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。和数字位数也无关系 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。

比如存int(3),可以存数值1234，"3"只是代表例如存12这个数值，如果指定了属性zerofill，代表‘0’填充，就是MySQL数据库会自动做0的填充，填到前面来，又因为指定的是“3”位，所以填充一个0。int(n),只是保证存储值的最小宽度是n位。通常情况下，不指定zerofill “n”是没有任何意义的。可以在phpmyadmin或navicat中手动设置，或alter table t1 modify id1 int zerofill;

对于decimal来说，它里面存储小数点的时候，无论小数后几位都可以保存下来，而float和double是有取值范围的。对于计算来说，float和double的计算效率会更高一些，decimal可以理解成当成字符串来处理的，所以它存储的内容会更大。

比如指定的是varchar(16)而存的是varchar(32),会导致这个32位的字符串被截成16位。有的mysql版本也可能会直接报错。

比如定义char(5),存一个三位的字符串，则会补两个空格。
存储密码的时候可以用char，如char(32),因为MD5
之后，无论多长的字符串都会变成32位。
注意一下，varchar可变长指的是只会往小的缩，而不会往大扩。char和varchar一旦超出预设值，都会截断。

BLOB是一个二进制大对象

定义一个性别，就可以用enum，ENUM("男", "女")，特点是值非常紧凑，把列表值压缩成1或2个字节。其内部存储的是整数，在这里其内部是ENUM("0", "1")这样来存储。

TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。
范围不同：datetime 以''YYYY-MM-DD HH:MM:SS''格式检索和显示DATETIME值。支持的范围为''1000-01-01 00:00:00''到''9999-12-31 23:59:59''TIMESTAMP值不能早于1970或晚于2037
关于存储微秒，可以考虑使用bigint：小数有很多位，用float和double可能会有损耗，可以把值乘以多少位例如100000，存进数据库，拿出来再除就行了。

-u用户名，-p密码，-h主机，-P端口
\G把打印的结果垂直显示格式化处理、\c取消当前MySQL的命令、\q退出mysql、\s显示MySQL的服务器状态、\h帮助信息、\d改变执行符，例如把分号；改成其他符号

总体来说innodb有优势，虽然myisam支持一些innodB不具备的，例如有全文索引、但mysiam的全文索引仅仅支持英文，所以实际很少去用。一般使用到用斯芬克斯，ES、卢森等等。

今天对比一下mysql两种常用存储引擎myisam和innodb的文件存储结构：
先说一下两者的大概区别：
myisam 更适合读取大于写入的业务，同时不支持事物。
innodb 支持事物，效率上比myisam稍慢。
文件存储：
myism物理文件结构为：
.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。
.myd文件：myisam存储引擎专用，用于存储myisam表的数据
.myi文件：myisam存储引擎专用，用于存储myisam表的索引相关信息
innodb的物理文件结构为：
.frm与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。
.ibd文件和.ibdata文件：
这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件
共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件
觉得使用哪种方式的参数在mysql的配置文件中 innodb_file_per_table