mysql

最新推荐文章于 2025-02-11 07:15:00 发布

原创最新推荐文章于 2025-02-11 07:15:00 发布 · 391 阅读

0 ·

CC 4.0 BY-SA版权

1.如计划在列上建索引，就尽量避免设计成可为null；timestamp列默认为not null

创建索引：alter table table_name add key(a(7));

2.应该使用32位无符号整数存储ip地址，比用字符串存好

3.mysql索引：B-Tree索引，它的叶子节点的指针指向被索引的数据，索引顺序在定义时确定，规则（必须按索引的最左列开始查找；不能跳过索引中的列；如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找，即这个查询只能使用该列及其左边的列作为索引）；简言之：查询只能使用索引的最左前缀，直到遇到第一个范围条件列，但有时候可以使用IN()技术来替代范围查询，但不是所有的范围查询都可以转换。所以，这里有个原则：尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列。

哈希索引，mysql中只有Memory引擎支持哈希索引。哈希索引只包含哈希值和行指针，而不存储字段值；无法排序；不支持部分索引列匹配查找。

空间数据索引；全文索引。

独立的列是指索引列不能是表达式的一部分，也不能是函数的参数（例 where id+1=5 , where f(id)=10;）

4.索引的优点：减少服务器所需扫描的数据量；帮助服务器避免排序和临时表；将随机I/O变为顺序I/O

5.前缀索引：select count(*) as a,b from table group by b order by a desc limit 10;应该使前缀的选择性接近于完整列的选择性，它的计算方法是

select count(distinct left(a,3))/count(*) as sel3,

count(distinct left(a,4))/count(*) as sel4,

count(distinct left(a,5))/count(*) as sel5,

.....................

from table;当发现sel*的值增长幅度很小时，说明选择性已经够了。

6. 多列索引不等于为每个列创建独立的索引或者按照错误的顺序创建多列索引。

7.解决组合翻页页数过大：使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行

select * from table inner join(select id from table where ...order by ... limit start,limit ) as x using (id);

一、MySQL5.6 触发器的使用（条件分支、变量的使用）

 
  create trigger check_record_delete_trigger after DELETE  
 on check_record FOR EACH ROW  
 begin  
   
 set @count = (select count(*) from check_record where INSTRUMENT_ID = old.INSTRUMENT_ID);  
   
 if @count = 0 then  
 set @Ins = 'drop Instrument';  
 delete from product_info where product_id = old.INSTRUMENT_ID and product_flag = 0;  
 elseif @count > 0 THEN  
 set @Ins = 'Keep it';  
 end if;  
   
   
 set @count = (select count(*) from check_record where REAGENT_ID = old.REAGENT_ID);  
   
 if @count = 0 then  
 set @Rea = 'drop Reagent';  
 delete from product_info where product_id = old.REAGENT_ID and product_flag = 1;  
 elseif @count > 0 THEN  
 set @Rea = 'Keep it';  
 end if;  
   
 end;  
 

这段代码的意思是，删除主表 check_record中的一条记录时，也要相应地删除product_info表中相关联的记录，用instrument_id和reagent_id关联。

因为product_info表中的prouct_id在check_record中体现为instrument_id和reagent_id两个字段（product_info表中包括两种记录：instrument和reagent），

并且在check_record中会有多条记录有相同的instrument_id和reagent_id，所以每次删除时要进行判断，如果check_record中还有instrument_id或者reagent_id存在，那么

procut_info中就不能删，反之如果check_record中已经没有了特定值的instrument_id和reagent_id，那么product_info中的对应记录也就失去了存在的意义，所以要删除。

有几个注意点：

1. 关于if else分支在mysql中的使用，只能出现在存储过程、函数或者触发器中，如果在控制台中普通的查询语句中出现if else是会报语法错误的。

2. 有if，就必须有end if，不然就报语法错误，这一点和java、JS等语言不一样，并且，end if后面必须加分号，不然报错。

3. 关于给变量赋值，必须以 set...开头，set不能省略，否则报语法错误。

4. 关于”old“，它指的是刚被删除的那条记录，也就是说，这条记录中的字段值还是可以拿出来用一下的。

三、mysql存储过程抛出异常

有时候，不希望存储过程抛出错误中止执行，而是希望返回一个错误码。 Mysql 支持异常处理，通过定义CONTINUE/EXIT 异常处理的 HANDLER 来捕获 SQLWARNING/NOT FOUND/SQLEXCEPTION （警告 / 无数据/ 其他异常）。其中， FOR 后面可以改为 SQLWARNING, NOT FOUND, SQLEXCEPTION 来指示所有异常都处理，相当于 oracle 中的 others 。例如，当不进行异常处理时，以下代码将直接抛出一个 ERROR 1062 (23000) 错误：

Sql code 4-1:

[c-sharp] view plain copy

CREATE PROCEDURE test_proc_ins1(
IN i_id INT,
IN i_name VARCHAR(100)
)
BEGIN
INSERT INTO testproc VALUES (i_id,i_name);
INSERT INTO testproc VALUES (i_id,i_name);
END;

经过异常处理后，可以避免抛出错误，而是定义一个返回参数 o_ret 赋予特殊值来表示失败，这样，在java 代码中，可以通过获取返回值而不是捕获异常的方式来处理业务逻辑。例如将返回值设置为 -1:

Sql code 4-2:

[c-sharp] view plain copy

CREATE PROCEDURE test_proc_ins1(
IN i_id INT,
IN i_name VARCHAR(100),
OUT o_ret INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '23000' set o_ret = -1;
-- 也可以这样使用：
-- DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set o_ret=-1;
INSERT INTO testproc VALUES (i_id,i_name);
INSERT INTO testproc VALUES (i_id,i_name);
set o_ret = 1;
END;

MySQL存储过程传参之in,out,inout参数用法：如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。 5范式： 6个范式，大的包含小的。

第一范式（1NF）：属性不可分；

第二范式（2NF）：符合1NF，并且，非主属性完全依赖于码。

第三范式（3NF）：符合2NF，并且，消除传递依赖； BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性。

BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。