MySQL 小知识点（记录一下）

平时使用mysql时，有些需要注意的地方写下来，方便以后参考使用

1、 update语句不能同时对同一张表进行set 赋值操作

update a set col=(select col from a where id='5') where id>5 and id<10;

如何是上面的写法就会报错了

ERROR 1093 (HY000): You can't specify target table 'a' for update in FROM clause

经过研究发现是 mysql 定义update语句不能同时对同一张进行set 赋值操作，也就是说 update a 的时候 不能在后面select colfrom a ，如果是不同表操作是没有问题的。

解决方法：

update a set col=(select col from (select * from a ) as b where id='5' )where id>5 and id <10;

2、 查询BLOB字段

select cast(full_msg as char)from table;

3、 mysql select 语句判断为空

select ifnull(null,0)

4、 mysql将表格所有查询数据（行、列）都合并成一个列显示(该函数有长度限制，如果参数中任意一个参数为Null 则返回结果为null)

    

 select cast(group_concat(ifnull(address,'') ifnull(found_date,'') info_id)as char) from table

5、CONCAT_WS（合并列数据， 长度限制比group_concat()函数长）

 注意：第一个参数必须为字符或者符号，否则结果是NUll ,第一个是分隔符，如果参数中任意一个参数为Null 则返回结果也将会是为null

select CONCAT_WS('.',ifnull(verifier,''),ifnull(writer,''),description,summary,ifnull(image_file,'') )   from table

6、sql 一个表数据导入另一个表部分数据

i<span style="font-size:12px;">nsert into cp_report_heading(chapter,serial,art</span>icle,heading_name)  select chapter,section,article,heading_name from report;

7、存储过程中如果需要使用如java中的for循环格式，则需要定义游标（三层循环自己写的小例子）

<span style="font-size:12px;">BEGIN
   DECLARE rid int;
   DECLARE pid int;
   DECLARE did int;
   DECLARE x int;
   DECLARE y int;
   DECLARE z int;
   DECLARE chapter1 int;
   DECLARE serial1 int;
   DECLARE article1 int;
   DECLARE paragraph1 int;
   DECLARE auditReportId int;
   DECLARE stageId int;
   DECLARE hid int;
   DECLARE str VARCHAR(300);  
   DECLARE str2 VARCHAR(300);  
   DECLARE str3 VARCHAR(300);
   DECLARE result int;
   DECLARE tableName VARCHAR(300);  
   
   # 定义游标 
   DECLARE report_detail_list CURSOR FOR select id as rid, chapter as chapter1,serial as serial1 ,article as article1,paragraph as paragraph1,
table_entity_name as tableName,audit_report_id as auditReportId,stage_id as stageId,report_heading_id as hid
from cp_report_detail where  audit_report_id = reportId and delete_flag = 0;
   # 定义循环结束的根据，根据rid值进行循环，直到rid为空
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET rid = NULL;
   # 打开游标
   OPEN report_detail_list; 
  # 获取数据放入上面定义的变量中
   fetch  report_detail_list into rid,chapter1,serial1,article1,paragraph1,tableName,auditReportId,stageId,hid; 
   # 根据rid开始循环  
   WHILE ( rid IS NOT NULL) DO
   #concat追加字符串
   set str =  concat(str,x);
   if tableName='测试字段' then
   BEGIN      ##如果需要多级循环，需要加 begin end
DECLARE result LONGTEXT;  
DECLARE param LONGTEXT;  
DECLARE detail_param LONGTEXT;  
DECLARE report_company_list CURSOR FOR select id as pid from cp_company_base_info where detail_id = rid;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET pid = NULL;
OPEN report_company_list; #二级循环
FETCH report_company_list into pid;
  WHILE(pid is NOT NULL) DO
   set param = "";
  set str2 =  concat(str2,y);
   select cast(group_concat(delete_flag,ifnull(description,''),ifnull(image_file,'') )  as char) into param from cp_company_base_info where id = pid;
   set result =  concat(ifnull(result,''),param);  ##将两个变量拼接成一个
   BEGIN
   DECLARE report_company_detail_list CURSOR FOR select id as did from cp_company_base_detail where info_id = pid;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET did = NULL;
   OPEN report_company_detail_list; #三级循环  
FETCH report_company_detail_list into did;
   
WHILE(did is NOT NULL) DO
  set str3 =  concat(str3,z);
  set detail_param = "";
## 将下面表中的所有的列拼装取出存入变量中   ##ifnull(address,'') 如果为空则用字符串代替
select cast(group_concat(ifnull(address,''), ifnull(company_name,'')， ifnull(startup_date,''))  as char) into  detail_param from cp_company_base_detail where id = did;
set result =concat(ifnull(result,''),detail_param);  ##将两个变量拼接成一个
fetch  report_company_detail_list into did;  
end while;  
   #关闭游标  
   CLOSE report_company_detail_list ;
   
   end;
  fetch  report_company_list into pid;  
  end while;  
#关闭游标  
CLOSE report_company_list ;
   end;
   end if;
 
   #读取下一行的数据  
   fetch  report_detail_list into rid,chapter1,serial1,article1,paragraph1,tableName,auditReportId,stageId,hid;  
end while;  
#关闭游标  
CLOSE report_detail_list ;


END
</span>