mysql 常见问题总结（一）

1. sql语句应该考虑哪些安全性？
   1) 防止sql注入，对特殊字符进行转义，过滤或者使用预编译的sql语句绑定变量。
   2) 最小权限原则，特别是不要用root账户，为不同的类型的动作或者组建使用不同的账户。
   3) 当sql运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。
2. 简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响。
   1) 索引是一种特殊的文件（InnoDB数据表上的索引是表空间的一个组成部分），它们包含着对数据表里所有记录的引用指针。
   2) 普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。
   3) 普通索引允许被索引的数据列包含重复的值，如果能确定某个数据列只包含彼此各不相同的值，在为这个数据索引创建索引的时候就应该用关键字UNIQE把它定义为一个唯一所以，唯一索引可以保证数据记录的唯一性。
   4) 主键，一种特殊的唯一索引，在一张表中只能定义一个主键索引，逐渐用于唯一标识一条记录，是用关键字PRIMARY KEY来创建。
   5) 索引可以覆盖多个数据列，如像INDEX索引，这就是联合索引。
   6) 索引可以极大的提高数据的查询速度，但是会降低插入删除更新表的速度，因为在执行这些写操作时，还要操作索引文件。
3. 一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ？
   1) 如果表的类型是MyISAM，那么是18。
   因为MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。
   2) 如果表的类型是InnoDB，那么是15。
   InnoDB表只是把自增主键的最大ID记录到内存中，所以重启数据库或者是对表进行OPTIMIZE操作，都会导致最大ID丢失。
4. 请简述项目中优化sql语句执行效率的方法，从哪些方面。sql语句性能如何分析？
   1) 尽量选择较小的列
   2) 将where中用的比较频繁的字段建立索引
   3) select子句中避免使用‘*’
   4) 避免在索引列上使用计算，not，in和<>等操作
   5) 当只需要一行数据的时候使用limit 1
   6) 保证表单数据不超过200w，适时分割表
   7) 针对查询较慢的语句，可以使用explain来分析该语句具体的执行情况
5. mysql_fetch_row()和mysql_fetch_array()的区别
   这两个函数，返回的都是一个数组，区别就是第一个函数返回的数组是只包含值，我们只能row[0],row[1],这样以数组下标来读取数据，而mysql_fetch_array()返回的数组既包含第一种，也包含键值对的形式，我们可以这样读取数据，（假如数据库的字段是 username,passwd）:row[‘username‘],row[‘passwd‘。
6. 数据库事务的四个特性及含义
   数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
   原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
   一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
   隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。
   持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚
7. drop,delete与truncate的区别
   drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。

（1） DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

（3） 一般而言，drop > truncate > delete

（4） 应用范围。TRUNCATE 只能对TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。

（6） truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7） delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8） truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9） 在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器.
8. 查询表中列的注释信息
select * from information_schema.columns where table_schema = ‘db’ #表所在数据库 and table_name = ‘tablename’ ; #你要查的表
9. 查看表生成的DDL
show create table table_name;
10. 给数据表创建索引
ALTER TABLE article ADD INDEX x ( category_id , comments, views );
11. 删除旧索引
DROP INDEX ndex_name ON table_name;
12. 复制表结构
create table table1 like table;
13. 复制数据
insert into table1 select * from table
14. 查询用户表
SELECT User, Host, Password FROM mysql.user;
15. 创建一个远程用户
create user test identified by ‘123456’;
16. 机器授权
grant all privileges on . to ‘test’@’%’identified by ‘123456’ with grant option;
all代表接受所有操作，比如 select,insert,delete….; . 代表所有库下面的所有表;% 代表这个用户允许从任何地方登录；为了安全期间，这个%可以替换为你允许的ip地址；
17. 刷新mysql用户权限相关表
flush privileges ;
18. 修改指定用户密码
update mysql.user set password=password(‘新密码’) where User=”test” and Host=”localhost”;
19. 删除用户
delete from user where User=’test’ and Host=’localhost’;
20. 查询数据直接插入
INSERT INTO 表名1(字段列表1) SELECT 字段列表2 FROM 表名2 WHER条件表达式;
• 表名1：表示记录插入到哪个表中。
• 字段列表1：表示为哪些字段赋值。
• 字段列表2：表示从表中查询出哪些字段的数据。
• 表名2：表示记录是从哪个表中查询出来的。
• 条件表达式：表示设置SELECT语句的查询条件。
将“表名2”中查询出来的记录插入到“表名1”中以后，“表名2”中仍然保存着原来的记录。
21. 从已存在的表选取数据建新表
CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,PRIMARY KEY (a), KEY(b))ENGINE=MyISAM SELECT b,c FROM test2;
22. 在指定某个字段后面添加新的字段
ALTER TABLE test ADD COLUMN mainId VARCHAR(36) DEFAULT NULL AFTER id;
23. 在表头添加一个新字段
ALTER TABLE test ADD id INT(8) auto_increment PRIMARY KEY FIRST;
24. 删除数据表中某个字段
ALTER TABLE table_name DROP COLUMN mainId;