MySQL数据库学习笔记

发表于   |   分类于   |  

常用字段类型(type)

MySQL中的数据类型大致可分为：日期和时间，数值以及字符串。各类型的详细介绍如下所示

  • 日期和时间数据类型

日期时间

  • 数值数据类型

整型

浮点

　注意：如果我们将字段定义为浮点型时，要注意定义时的浮点精度。比如我们将字段的类型定义为：float（6，3）；数据库存储该字段值则是整数占３位，小数占３位，可参看如下测试案例：
　1、插入123.111，最后查询到的结果是123.111；
　2、插入1111.12，最后查询到的结果是999.120；
　3、插入12.1234，最后查询到的结果是12.123；

  • 字符串数据类型

字符串类型

　说明：
　１、char（n）和varchar（n）中括号中的n代表字符的个数，而不是字节的个数，所以当使用了中文的时候(UTF8编码格式)意味着可以插入m个中文，但是实际会占用m*3个字节；
　2、char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n；
　3、超过char和varchar的n设置后，字符串会被截断；
　4、char的上限是255个字节，varchar的上限为65535字节，text的上限同样为65535个字节；
　5、char在存储的时候会截断尾部的空格，varchar和text不会；
　6、varchar会使用1~3个字节来存储长度，text不会；

  • 其它类型

　1、enum（“member1”，“member2”，….”memeberN”）
　enum数据类型就是定义一种枚举，最多包含65535个不同的成员，字段的值限制为枚举中的值。如果字段属性声明包含NULL属性，则NULL将被认为是一个有效值，并且是默认值，如果声明了NOT NULL属性，则枚举的第一个成员是默认值；

常用字段属性

  • 1、auto_increment

　auto_increment能为表中新插入的记录赋一个唯一的整数标识符。为字段赋此属性，字段值会在表中新插入一条记录后再原来的值基础上自动加1。

　MySQL要求将auto_increment属性用于作为主键的列。

  • 2、binary

　binary属性只用于char和varchar值。当为字段指定了该属性时，将以区分大小写的方式排序。与之相反，忽略binary属性时，将使用不区分大小写的方式排序。例如：

　hostname char(25) binary not null

  • 3、default

　default属性确保在任何值可用的情况下，赋予某个常量值，因为MySQL不允许插入函数或表达式值。此属性无法用于类型为BLOB或text字段。

  • 4、not null

　如果将一个字段定义为not null，将不允许向该字段插入null值。建议在重要情况下始终使用not null属性，因为它提供一个基本验证，确保已经向查询传递了所有必要的值，字段的默认属性是not null。

  • 5、null

　为字段指定null属性时，该列可以保持为空，而不论行中其它字段是否填充，注意：null的准确说法是“无”，而不是空字符串或0。, 判断值是否等于NULL，不能简单用=，因为任何数跟NULL进行运算都是NULL，而要用IS NULL关键字。

  • 6、primary key

　primary key属性用于确保指定行的唯一性。指定为主键的字段，其值不能重复，也不能为空。为指定为主键的字段赋予auto_increment属性是很常见的，因为此字段不必与行数据有任何关系，而只是作为一个唯一标识符。

  • 7、unique

　被赋予unique属性的字段将确保所有值都有不同的值，如果该字段设置可为null，则null值可以重复。一般会指定一个列为unique，以确保该列的所有值都不同。

  • 8、zerofill

　zerofill属性可用于任何数值类型，用0填充所有剩余字段空间。例如，无符号int的默认宽度是10；因此，当“零填充”的int值为4时，将表示它为0000000004。例如：

　orderid int unsigned zerofill not null

  • 9、unsigned

　unsigned 属性只针对整型，而binary属性只用于char 和varchar。采用unsigned声明的类型为int的字段，取值范围为0到4294967295，正好可用于表示unix时间戳，时间范围到了2016年；一般unix时间戳都采用unsigned　int表示；

ALTER TABLE….ADD命令

  • 1、添加索引

　mysql>alert table 表名 add index 索引名 (字段名1[,字段名2…])；
　eg：mysql>alter table table1 add index index_1(name)；

  • 2、添加主关键字索引

　mysql>alter table 表名 add primary key(字段名)；
　eg：mysql>alter table table1 add primary key(id)；

  • 3、添加唯一限制条件的索引

　mysql>alert table 表名 add unique 索引名(字段名)；
　eg：mysql>alter table table1 add unique unique_1(username)；

  • 4、添加外键

　mysql>ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主表名(主表主键名) [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}] [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}]；
　eg：mysql>alter table table1 add constraint fk_wan foreign key(userId) on delete cascade on update cascade；

  • 5、添加字段

　mysql>ALTER TABLE 表名 ADD 字段名 字段类型 字段属性 AFTER 字段名；
　eg：alert table table1 add password varchar(20) not null after username；

ALTER TABLE …DROP命令

  • 1、删除索引

　mysql>ALTER TABLE 表名 DROP INDEX 索引名；
　eg：mysql>alter table table1 drop index index_1;

  • 2、删除主键

　mysql>ALTER TABLE 表名 DROP PRIMARY KEY；
　eg：mysql>alter table table1 drop primary key；

  • 3、删除唯一索引

　mysql>ALTER TABLE 表名 DROP UNIQUE 唯一索引名；
　eg：mysql>alter table table1 drop unique unique_1；

  • 4、删除外键

　mysql>ALTER TABLE 表名 DROP FOREIGN KEY 外键名；
　eg：mysql>alter table table1 drop foreign key FK_1；

  • 5、删除字段

　mysql>ALTER TABLE 表名 DROP [COLUMN] 字段名；
　eg：mysql>alter table table1 drop username；

重命名命令

  • 1、更改字段名

　mysql>ALTER TABLE 表名 CHANGE 原字段名 现字段名 字段类型 字段属性；
　eg：mysql>alter table table1 change name username varchar(24) not null；

  • 2、更改表名

　mysql>ALTER TABLE 表名 RENAME 新表名；
　eg：mysql>alter table table1 rename table2；

  • 3、更改存储引擎

    　mysql>ALTER TABLE 表名 ENGINE = 引擎类型；
    　eg：mysql>alter table table1 engine = MyISAM；

  • 4、更改charset

　mysql>ALTER TABLE 表名 charset = 编码类型；
　eg：mysql>alter table table1 charset = utf8；

MySQL出现乱码原因

  • 存入MySQL经历的编码转换过程

存入转换图

上图中有3次编码/解码的过程（红色箭头）。三个红色箭头分别对应：客户端编码(1次)，MySQL Server解码(2次)，Client编码向表编码的转换(3次)。其中Terminal可以是一个Bash，一个web页面又或者是一个APP。本文中我们假定Bash是我们的Terminal，即用户端的输入和展示界面。图中每一个框格对应的行为如下：

　1、在terminal中使用输入法输入；
　2、terminal根据字符编码转换成二进制流；
　3、二进制流通过MySQL客户端传输到MySQL Server；
　4、Mysql Server通过character-set-client解码；
　5、判断character-set-client和目标表的charset是否一致；
　6、如果不一致则进行一次从client-charset到table-charset的一次字符编码转换；
　7、将转换后的字符编码二进制流存入文件中；

  • 从MySQL表中取出数据经历的编码转换过程

取出转码过程

上图中也有3次编码/解码的过程（红色箭头）。上图中三个红色箭头分别对应：客户端解码展示（3），MySQL Server根据character-set-client编码（2），表编码向character-set-client编码的转换（1）。

　1、从文件读出二进制数据流；
　2、用表字符集编码（charset）进行解码；
　3、将数据转换为character-set-client的编码；
　4、使用character-set-client编码为二进制流；
　5、Server通过网络传输到远端client；
　6、client通过bash配置的字符编码展示查询结果；

说明：由以上解释可知，只要做到“三位一体”，即客户端，MySQL character-set-client，table charset三个字符集完全一致就可以保证一定不会有乱码出现了。

  • 关于错进错出

　在MySQL中最常见的乱码问题的起因就是把错进错出神话。所谓的错进错出就是，客户端(web或shell)的字符编码和最终表的字符编码格式不同，但是只要保证存和取两次的字符集编码一致就仍然能够获得没有乱码的输出的这种现象。但是，错进错出并不是对于任意两种字符集编码的组合都是有效的。我们假设客户端的编码是C，MySQL表的字符集编码是S。那么为了能够错进错出，需要满足以下两个条件

MySQL接收请求时，从C编码后的二进制流在被S解码时能够无损；
MySQL返回数据是，从S编码后的二进制流在被C解码时能够无损；

  • 关于MySQL的编码解码

　既然系统之间是按照二进制流进行传输的，那直接把这串二进制流直接存入表文件就好啦。为什么在存储之前还要进行两次编解码的操作呢？

1、Client to MySQL Server的编解码的原因是MySQL需要对传来的二进制流做语法和词法解析。如果不做编码解析和校验，我们甚至没法知道传来的一串二进制流是insert还是update。
2、File to Engine的编解码是为知道二进制流内的分词情况。举个简单的例子：我们想要从表里取出某个字段的前两个字符，执行了一句形如select left(col,2) from table的语句，存储引擎从文件读入该column的值是E4B8ADE69687。那么这个时候如果我们按照GBK把这个值分割成E4B8,ADE6,9687三个字，并那么返回客户端的值就应该是E4B8ADE6；如果按照UTF8分割成E4B8AD,E69687，那么就应该返回E4B8ADE69687两个字。可见，如果在从数据文件读入数据后，不进行编解码的话在存储引擎内部是无法进行字符级别的操作的。

MySQL的引擎

  • 存储引擎是什么？

　MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
　例如，如果你在研究大量的临时数据，你也许需要使用内存存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)。MySQL默认的存储引擎是InnoDB，可使用“show engines”查看；

mysql存储引擎

  • MyISAM与InnoDB区别

1、MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持；
　所谓事务处理，就是原子性操作。
打个比方，支持事务处理的Innodb表，建设一个中，发帖是给积分的。你发了一个帖子执行一个insert语句，来插入帖子内容，插入后就要执行一个update语句来增加你的积分。假设一种特殊情况突然发生，insert成功了，update操作却没有被执行。也就是说你发了帖子却没有增加相应的积分。这就会造成用户不满。如果使用了事务处理，insert和update都放入到事务中去执行，这个时候，只有当insert和update两条语句都执行生成的时候才会将数据更新、写入其中，如果其中任何一条语句失败，那么就会回滚为初始状态，不执行写入。这样就保证了insert和update肯定是一同执行的。

2、MyISAM类型不支持外键，而InnoDB支持；
3、在执行数据库写入操作时（insert，update，delete），MyISAM会锁表，而InnoDB会锁行；
　通俗点说，就是你执行了一个update语句，那么mysiam表会将整个表都锁住，其他的insert和delete、update都会被拒之门外，等到这个update语句执行完成后才会被依次执行。而锁行，就是说，你执行update语句是，只会将这一条记录进行锁定，只有针对这条记录的其他写入、更新操作会被阻塞并等待这条update语句执行完毕后再执行，针对其他记录的写入操作不会有影响。

　因此，当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快

参考

10分钟学会理解和解决MySQL乱码问题
C语言中文网
白话讲MyISAM和InnoDB的区别

　

热评文章