mysql逻辑删除和物理删除 mysql三种删除语句的区别

标题

• MySQL删除表操作（delete、truncate、drop的区别）

• 1. 语言维度：
• 2.执行特点：
• 3. 作用维度：
• 4. 恢复维度：
• 5. 效率维度：
• 6. 内存维度：
• 7. 其它区别
• 8. 应用维度

• DDL 和 DML 的说明
• 针对上面问题扩展了解知识

• MySQL中的 MyISAM、InnoDB跟区别
• [mysql触发器trigger 实例详解]()

• Mysql char 和 varchar 的区别

• VARCHAR和TEXT、BlOB类型的区别

• sql注入总结--详细

MySQL删除表操作（delete、truncate、drop的区别）

1. 语言维度：

delete： 属于DML语言
truncate： 属于 DDL 语言
drop： 属于 DDL 语言

2.执行特点：

delete:每次删除一行，都在事务日志中为所删除的每行记录一项。产生rollback，事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发，如果删除大数据量的表速度会很慢。
删除表中数据而不删除表的结构(定义)，同时也不释放空间。

truncate默认情况下，truncate通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。所以使用的系统和事务日志资源少，可以使用reuse storage; truncate会将高水线复位(回到最开始).
truncate是DDL语言, 操作立即生效,自动提交，原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
删除内容、释放空间但不删除表的结构(定义)。

3. 作用维度：

delete: 操作表中的每一行数据（删除表中的数据）
1、删除整张表的数据:delete from table_name;
2、删除部分数据，添加where子句：delete from table_name where…;

truncate: 操作整张表（清空整张表数据）
1、只能操作表，将表中数据全部删除，在功能上和不带where子句的delete语句相同：
truncate table table_name;

drop: 操作整张表（删除表的结构）
1、drop语句将删除表的结构，以及被依赖的约束(constrain),触发器(trigger),索引(index);
drop table table_name;
2、说明
　　1）、删除之后，依赖于该表的存储过程/函数将保留,但是变为invalid状态.
　　2）、drop也属于DDL语言，立即执行，执行速度最快
　　3）、删除内容和定义，释放空间。

4. 恢复维度：

delete： 可恢复
truncate： 不可恢复
drop： 不可恢复

5. 效率维度：

drop > truncate > delete

6. 内存维度：

表和索引所占空间：
　　当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；
　　DELETE操作不会减少表或索引所占用的空间；
　　DROP语句将表所占用的空间全释放掉。
注意：
　　drop直接删除表,删除较干净彻底.删除表全部数据和表结构，立刻释放磁盘空间，不管是 Innodb 和 MyISAM;
　　truncate删除表全部数据，保留表结构，立刻释放磁盘空间 ，不管是 Innodb 和 MyISAM;
　　delete 删除表全部数据，表结构不变，对于 MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
　　delete 带条件的删除，表结构不变，不管是 innodb 还是 MyISAM 都不会释放磁盘空间;
　　delete 操作以后，使用 optimize table table_name 会立刻释放磁盘空间，不管是 innodb 还是 myisam;
　　delete from 表以后虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以使用这部分空间。

7. 其它区别

TRUNCATE 只能对table； DELETE可以是table和view。
　delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。
　DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
　　TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
　　当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。
　　如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；
　使用truncate删除之后，种子会恢复到初始值。

8. 应用维度

以上特点就是应用维度的选择
1、delete 语句可以使用where子句实现部分删除，而truncate不可以，会将表中的整个数据全部删除，使用时，可以按需求选择；
2、如果想从表中删除所有的数据，不要使用delete，可以使用truncate语句，因为这样执行速度更快。truncate语句实际是删除原来的表然后重新建立一个新表；
3、在没有备份情况下，谨慎使用 drop 与 truncate。要删除表结构使用drop;
4、对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。(外键约束时,不能单独删除数据)

方式1：如果要删除的数据库存在，则删除成功。如果不存在，则报错	
DROP DATABASE mytest1;
方式2：推荐。 如果要删除的数据库存在，则删除成功。如果不存在，则默默结束，不会报错。
DROP DATABASE IF EXISTS mytest1;    


 select * from admin where  adminId = '20402702611292760367'
 SET autocommit = FALSE; -- 删除后可回滚
 DELETE  from admin where  adminId = '20402702611292760367'
 ROLLBACK;

在速度上，一般来说，drop> truncate > delete。

如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关(不能回滚)，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。

truncate只能作用于表；delete，drop可作用于表、视图等。

truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。

truncate会重置表的自增值；delete不会。

truncate不会激活与表有关的删除触发器；delete可以。

truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

DDL 和 DML 的说明

① DDL（CREATE 、 DROP 、 ALTER、TRUNCATE 等）的操作一旦执行，就不可回滚。指令SET autocommit = FALSE对DDL操作失效。(因为在执行完DDL操作之后，一定会执行一次COMMIT。而此COMMIT操作不受 SETautocommit = FALSE影响。)

② DML（INSERT 、 DELETE 、 UPDATE 、 SELECT等）的操作默认情况，一旦执行，也是不可回滚的。但是，如果在执行DML之前，执行了 SET autocommit = FALSE，则执行的DML操作就可以实现回滚。

针对上面问题扩展了解知识

MySQL中的 MyISAM、InnoDB跟区别

InnoDB：	MyISAM：
支持事务处理等	不支持事务，回滚将造成不完全回滚，不具有原子性
不加锁读取	不支持外键
支持外键	不支持外键
支持行锁	支持全文搜索
不支持FULLTEXT类型的索引	保存表的具体行数,不带where时，直接返回保存的行数
不保存表的具体行数，扫描表来计算有多少行
DELETE 表时，是一行一行的删除	DELETE 表时，先drop表，然后重建表
InnoDB 把数据和索引存放在表空间里面	MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
跨平台可直接拷贝使用	跨平台很难直接拷贝
InnoDB中必须包含AUTO_INCREMENT类型字段的索引	MyISAM中可以使AUTO_INCREMENT类型字段建立联合索引
表格很难被压缩	表格可以被压缩
选择：
因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。
如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。
两种类型都有自己优缺点，选择那个完全要看自己的实际情况。

mysql触发器trigger 实例详解

Mysql char 和 varchar 的区别

这块我觉得脱离了版本跟引擎可能会出现不一样的情况。下面简要说说大致区别

char 和 varchar 类型相似,都是用来存储字符串,但是它们 存储 和 检索 的方式不同,
char 属于固定长度的字符类型,而 varchar 属于长度可变的字符类型。

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间
VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间

char简介
CHAR(20)最多可以容纳20个字符。
如char(8)，则数据库会使用固定的1个字节(八位)来存储数据，不足8位的字符串在其后补空字符。
char(10)，插入的值为"abc"这一字符串时，它们占的空间一样是10个字节，因为剩余7个位置被空格填充。

特点：
存储空间固定。
长度不够时内部存储使用空格填充。
若字段本身末尾存在空格，检索出来自动截断末尾空格（因为分不清空格是字段含有的还是填充产生的）。
若字段本身前端存在空格，是不会截断的。
当输入的字符长度超过指定长度时，char会截取超出的字符。
CHAR数据类型的长度可以是从0到255的任何值。
当存储CHAR值时，MySQL将其值与空格填充到声明的长度。
当查询CHAR值时，MySQL会删除尾部的空格。
请注意，如果启用PAD_CHAR_TO_FULL_LENGTH SQL模式，MySQL将不会删除尾随空格。


比较MySQL CHAR值：
存储或比较CHAR值时，MySQL使用分配给列的字符集排序规则。
使用比较运算符比较CHAR值时，MySQL不会考虑尾随空格，例如：=，<>，>，<等等。
请注意，当您使用CHAR值进行模式匹配时，LIKE运算符会考虑尾随空格。

MySQL CHAR和UNIQUE索引：
如果CHAR列具有UNIQUE索引，并且插入的值有多个尾随空格不同的值，则MySQL将拒绝因重复键错误而要求您进行的更改。
请参见以下示例
首先，为mysql_char_test表的status列创建唯一的索引。
CREATE UNIQUE INDEX uidx_status ON mysql_char_test(status);
其次，在mysql_char_test表中插入一行。
INSERT INTO mysql_char_test(status) VALUES('N ');
Error Code: 1062. Duplicate entry 'N' for key 'uidx_status'
 



适用情况：
适合存储很短的或者长度接近同一个长度的字符串。
char 比 varchar 在 存取上更具效率， 因为它是固定长度。
对于非常短的列，char比varchar在存储空间上也更有效率。

总结：

如果要存储的数据是固定大小，则应使用CHAR数据类型。 在这种情况下，与VARCHAR相比，您将获得更好的性能。
1、char 类型是固定长度的,Mysql 在程序处理的时候不需要计算长度,所以它的速度比 varchar 要快,
但是其缺点是浪费存储空间,插入时如果长度不够会在尾部以空格填充,可以看做是一种以空间换时间的方法。
查询时 Mysql 程序会对尾部的空格进行处理,浪费性能,综合来说,
如果业务需求中字段长度变化不大的情况下使用 char 类型来存储(例如 性别 字段,不是 0 就是 1,可以设计成 char(1) )

2、在使用 varchar 类型的时候不能因为其长度可变就都为 varchar 定义一个很大的长度,
实际开发中仍然要根据业务需求来设计合适的长度,定义一个远超实际需求长度的字段可能会影响 Mysql 程序的性能,
并且还有可能出现一些未知的 BUG,所以设计 varchar 类型的长度时,一定要根据业务需求去评估,
选择一个合适的长度。从而节省空间，它可以看做是一种用时间换取空间的方法。

比较常用的char、varchar、text的区别

1、长度的区别，char范围是0～255，varchar最长是64k，但是注意这里的64k是整个row的长度，
要考虑到其它的column，还有如果存在not null的时候也会占用一位，对不同的字符集，
有效长度还不一样，比如utf8的，最多21845，还要除去别的column，但是varchar在一般情况下存储都够用了。
如果遇到了大文本，考虑使用text，最大能到4G。

2、效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char

3、char和varchar可以有默认值，text不能指定默认值

4、数据库选择合适的数据类型存储还是很有必要的，对性能有一定影响。这里在零碎记录两笔，对于int类型的，
如果不需要存取负值，最好加上unsigned；对于经常出现在where语句中的字段，
考虑加索引，整形的尤其适合加索引。

首先需要知道汉字和字节字符的关系，关系如下：

1、 1个汉字 = 1个字 = 1个字符
2、 1个字符 = 1个字节 = 8bit（ACSII码下）
3、 1个字符 = 2个字节 = 16bit（Unicode码下）

字符并不是计算机语言， 一般情况下，汉字一个字占两个字节。英文字母一个字母占一个字节。

我试了下char(2)和varchar(2), 都能存2个英文字母或者2个汉字, varchar(10) 既10个字符可以存10个汉字，
不太明白这个n是表示字节还是字符，查了下资料：

1、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不会。

我使用的mysql版本是14.14，括号中的数字指的是长度，char范围是0～255，varchar最长是64k。
char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char。
所以综上这个字段设计时候我写成了varchar(5000)，虽然看起来比较奇怪，但是比text更合适一点。

VARCHAR和TEXT、BlOB类型的区别

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，
而不是取决于类型 的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，
实际的存储需要是字符串的长度 ，加上1个字节以记录字符串的长度。对于字符串‘abcd’，
L是4而存储要求是5个字节。

BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。
VARCHAR需要定义大小，有65535字节的最大限制；TEXT则不需要。
如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、
MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB 可以储存图片，TEXT不行，TEXT只能储存纯文本文件。4个TEXT类型TINYTEXT、TEXT、MEDIUMTEXT
和LONGTEXT对应于 4个BLOB类型，并且有同样的最大长度和存储需求。在BLOB和TEXT类型之间的唯一差别是
对BLOB值的排序和比较以大小写敏感方式执行，而对 TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大
小写不敏感的BLOB。