将sql存储过程转换成mysql存储过程要改动的地方积累

创建方式:

SQL

CREATE  PROCEDURE  group

BEGIN

END

mysql要带参数 即使没有参数也要带括号 

CREATE  PROCEDURE `group`()

BEGIN

END

注释:

sql

--修改面积

mysql

#修改面积

每句结尾的分号

sql

update rooms set  group_id=area.id

mysql

update rooms set  group_id=area.id;

临时表的用法:

临时表有如下限制(所以脚本中的表都改用实体临时表):

在同一个query语句中，你只能查找一次临时表 否则会报错:

遇到的错误-----MySQL使用临时表 出现 “ERROR 1137 (HY000): Can't reopen table” 的异常 解决方法

sql

CREATE PROCEDURE [dbo].[spGetChildren]
   @id AS int 
 AS
 BEGIN
     declare @temp table(tid int)
     insert @temp select dept_id from department d where d.dept_id = @id
     while @@rowcount > 0
         insert @temp select d.dept_id from department as d inner join @temp as t
         on d.up_dept_id = t.tid and d.dept_id not in(select tid from @temp)
 DELETE FROM @temp WHERE tid=@id
     select * from @temp
 END
mysql
CREATE PROCEDURE spGetChildren(id  int)   
 BEGIN
CREATE TEMPORARY  TABLE IF NOT EXISTS temp(tid int);
 CREATE TEMPORARY  TABLE IF NOT EXISTS temp1 (tid int);
 CREATE TEMPORARY  TABLE IF NOT EXISTS temp2 (tid int);
 insert into temp select dept_id from department d where d.dept_id = id;
 WHILE ROW_COUNT() > 0 DO
      select * from  temp;
     delete from temp1;
     delete from temp2;
     insert into temp1 select tid  from temp;
     insert into temp2 select tid  from temp;
     insert temp select d.dept_id from department as d inner join temp1 as t
         on d.up_dept_id = t.tid and d.dept_id not in (select tid from temp2);
 END WHILE;
 DELETE FROM temp WHERE tid=id;
 select * from  temp;
 delete from temp;
 END
日期加减:
sql
减
DATEADD(day,-1,GETDATE())
加
DATEADD(day,1,GETDATE())

mysql
减
date_sub(now(),interval 1 day)
加
date_add(now(),interval 1 day)

CONVERT
sql
CONVERT(date,'2013-12-23')
mysql
CONVERT('2013-12-23',date)

cast(rise as numeric(12))
mysl 中用 cast(rise as DECIMAL(12))
 

mysql中的cast和 convert 只能用以下类型 请自行判断转换:
这个类型 可以是以下值其中的 一个： 
BINARY[(N)] 
CHAR[(N)]
 DATE 
DATETIME 
DECIMAL
 SIGNED [INTEGER] 
TIME
 UNSIGNED [INTEGER]  


select .... into ...
mysql中会报错  Undeclared variable: temp 
mysql不支持select...into...语法，可以使用INSERT INTO ... SELECT .. 代替它。

ISNULL
sql
ISNULL(字段,0)

mysql
IFNULL(bd.ableFlag,0)

full join
 
Oracle 、DB2、SQL Server、PostgreSQL 支持 Full JOIN
 但是 MySQL 是不支持的。
 可以通过 
 LEFT JOIN  +   UNION  +  RIGHT JOIN 的方式 来实现。 

 
例子一:  
 
下面是 测试表与测试数据
 -- 学生表
 CREATE TABLE student (
   SNO    INT,
   SNAME  VARCHAR(10),
   ICNO   INT
 );
  
 -- 食堂IC卡表
 CREATE TABLE IC (
   ICNO     INT,
   ICNAME   VARCHAR(10),
   ICMoney  INT
 ); 
 INSERT INTO student VALUES(1, '张三', 1   );
 INSERT INTO student VALUES(2, '李四', 2   );
 INSERT INTO student VALUES(3, '王五', NULL); 
  
 INSERT INTO IC VALUES(1, '张三', 500 );
 INSERT INTO IC VALUES(2, '李四', 250 );
 INSERT INTO IC VALUES(3, '赵老师', 600);

 下面是 SQL Server  使用  full  join  的结果
 SELECT
   student.SNO,
   student.SNAME,
   IC.ICNAME,
   IC.ICMoney
 FROM
   student
     FULL JOIN IC
       ON (student.ICNO = IC.ICNO);
 go
 SNO         SNAME      ICNAME     ICMoney
 ----------- ---------- ---------- -----------
           1 张三         张三                 500
           2 李四         李四                 250
           3 王五         NULL              NULL
        NULL NULL       赵老师                600
  
 (4 行受影响)

 下面是 MySQL 不支持 的输出结果：
 mysql> SELECT
     ->   student.SNO,
     ->   student.SNAME,
     ->   IC.ICNAME,
     ->   IC.ICMoney
     -> FROM
     ->   student
     ->     FULL JOIN IC
     ->       ON (student.ICNO = IC.ICNO);
 ERROR 1054 (42S22): Unknown column 'student.SNO' in 'field list'


下面是 MySQL 的 LEFT JOIN  +   UNION  +  RIGHT JOIN 的方式。
 mysql> SELECT
     ->   student.SNO,
     ->   student.SNAME,
     ->   IC.ICNAME,
     ->   IC.ICMoney
     -> FROM
     ->   student
     ->     LEFT JOIN IC
     ->       ON (student.ICNO = IC.ICNO)
     -> UNION
     -> SELECT
     ->   student.SNO,
     ->   student.SNAME,
     ->   IC.ICNAME,
     ->   IC.ICMoney
     -> FROM
     ->   student
     ->     RIGHT JOIN IC
     ->       ON (student.ICNO = IC.ICNO);
 +------+-------+--------+---------+
 | SNO  | SNAME | ICNAME | ICMoney |
 +------+-------+--------+---------+
 |    1 | 张三  | 张三   |     500 |
 |    2 | 李四  | 李四   |     250 |
 |    3 | 王五  | NULL   |    NULL |
 | NULL | NULL  | 赵老师 |     600 |
 +------+-------+--------+---------+
 4 rows in set (0.01 sec)原文链接:
mysql实现full join
 
 
 
 
例子二:
01 求在A中不在B中的结果
SELECT A.customersid, '0' as IsOrder,'1' as IsBirth
FROM cs_nowbirthcust A left JOIN cs_precalloutcust B ON A.customersid = B.customersid
where B.customersid is null
11求在A中也在B中的结果
SELECT B.customersid, '1' as IsOrder,'1' as IsBirth
FROM cs_nowbirthcust A inner JOIN cs_precalloutcust B ON A.customersid = B.customersid
10求在不在A中在B中的结果
SELECT B.customersid, '1' as IsOrder,'0' as IsBirth
FROM cs_nowbirthcust A right JOIN cs_precalloutcust B ON A.customersid = B.customersid
where a.`customersid` is null

使用union all联合结果集合 01， 11， 10
即：
SELECT A.customersid, '0' as IsOrder,'1' as IsBirth
FROM cs_nowbirthcust A left JOIN cs_precalloutcust B ON A.customersid = B.customersid
where B.customersid is null
union all
SELECT A.customersid, '1' as IsOrder,'1' as IsBirth
FROM cs_nowbirthcust A inner JOIN cs_precalloutcust B ON A.customersid = B.customersid
union all
SELECT b.customersid, '1' as IsOrder,'0' as IsBirth
FROM cs_nowbirthcust A right JOIN cs_precalloutcust B ON A.customersid = B.customersid
where a.`customersid` is null

update set  from  table where....

在mysql中，用一个表的字段填充另一个表，(也许是版本的缘故，)不能直接使用set
select的结果，即：
UPDATE tableA
SET columnA=tableB.columnB
FROM
(SELECT columnB FROM tableB WHERE ...)
WHERE ...
方法/步骤
应该使用inner join，即：
UPDATE friends INNER JOIN users ON friends.friendid=users.userid
SET friends.friendname=users.username

原文链接:

mysql下使用update set from select

sql

update t_p_rooms set t_p_totalpricegroup_id=total.id 
from t_p_rooms rm,t_p_totalpricegroup total
where rm.t_p_totalpricegroup_id is null and
 rm.totalprice>=total.down and rm.totalprice<total.up;

MYSQL

update t_p_rooms rm
INNER JOIN  t_p_totalpricegroup total
ON rm.t_p_totalpricegroup_id is null and
 rm.totalprice>=total.down and rm.totalprice<total.up set t_p_totalpricegroup_id=total.id;

mysql用变量做表名 

@tablename

在sql中我们可以 自己拼凑变量用作表名

但mysql中不行。

mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp_executesql调用该语句。 

sql

SELECT COUNT(*)  INTO @num_count FROM  tbl_name WHERE rng = para_rng
                     AND north_latitude = para_north
                     AND bck_datetime =  para_datetime
                       AND east_longitude = para_east;

mysql

SET  tbl_name = CONCAT("20131226", tablename);
SET @STMT := CONCAT("SELECT COUNT(*) INTO @num_count FROM ", tbl_name
                      ," WHERE rng = '", para_rng
                      ,"' AND north_latitude = ", para_north
                      ," AND bck_datetime = '", para_datetime
                      ,"' AND east_longitude = ", para_east, ";");
  PREPARE STMT FROM @STMT;
  EXECUTE STMT; 

MYSQL存储过程中 表名 使用变量

row_number() over(partition by ...order by...)

sql
select t_p_rooms_keyid
 ,ROW_NUMBER() over(partition by t_p_baseinfo_id,t_p_building_id,t_p_batchinfo_id,rise order by t_p_rooms_keyid) as rank
 from t_p_rooms 
 where rise is not null
mysql
 select t_p_rooms_keyid,rank from 
 
 ( 
 
 select a.t_p_rooms_keyid,a.t_p_baseinfo_id,a.t_p_batchinfo_id,a.t_p_building_id,a.rise,@rownum=@rownum+1, 
 
 if(@bsid=a.t_p_baseinfo_id and @btid=a.t_p_batchinfo_id and @bdid=a.t_p_building_id and @rise=a.rise,@rank:=@rank+1,@rank:=1) as rank, 
 
 @bsid:=a.t_p_baseinfo_id,@btid:=a.t_p_batchinfo_id,@bdid:=a.t_p_building_id,@rise:=a.rise 
 
 from 
 
 (select t_p_baseinfo_id,t_p_rooms_keyid,t_p_batchinfo_id,t_p_building_id,rise from t_p_rooms  where rise is not null order by t_p_baseinfo_id,t_p_batchinfo_id,t_p_building_id,rise asc 
 
 ) a,(select @rownum:=0,@bsid:=null,@btid:=null,@bdid=null,@rise:=null,@rank:=0) b) c 
 

 

while  ...begin....end 循环 
sql
while a = 0
begin  
exec dbo.Proc_RoomsBuildType @t_p_building_keyid;
fetch next from MyCursor into t_p_building_keyid;
end 
mysql
 while a = 0   
 
do 
 
call  Proc_RoomsBuildType(t_p_building_keyid); 
 
fetch next from MyCursor into t_p_building_keyid; 
  
 
end  while; 
 


SET NOCOUNT ON

mysql中没找到具体用法 欢迎提供

游标的用法:

1.游标的定义:

游标(cursor)必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明。

也就是 放在begin 之后 与 处理程序之前是 ok的 

放在处理程序中间无法编译通过

2.@@FETCH_STATUS

sql
while  @@FETCH_STATUS = = 0
begin  
exec dbo.Proc_RoomsBuildType @t_p_building_keyid;
fetch next from MyCursor into t_p_building_keyid;
end  
 
mysql中没有@@FETCH_STATUS系统变量  会出现 unknown system variable FETCH_STATUS 错误
mysql中的用法是给它定义一个结束标识
declare done int default 0;  
#注意这句的顺序 不能放在 declare MyCursor  curcor后面，否则会报错(ERROR 1337: Variable or condition declaration after cursor or handler declaration)
declare MyCursor cursor for
select t_p_building_id 
from t_p_building
where t_p_buildingtype_id is null or t_p_buildingtype_id=1
order by t_p_building_keyid;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open MyCursor;
fetch next from MyCursor into t_p_building_keyid;  
while done <> 1
do 
           call Proc_RoomsBuildType(t_p_building_keyid);
fetch next from MyCursor into t_p_building_keyid;
end while;  
CLOSE MyCursor;

3.fecth into的值

FETCH NEXT FROM authors_cursor into  a; //a是我们定义的变量 且不能跟 实体表中的字段同名 否则会fetch失败

这里有一个比较坑的地方，注意，变量的定义不要和你的select的列的键同名！不然，fetch into 会失败！

spilt函数

在mysql中并没有split函数，需要自己写：

获取分割后的字符串个数的函数:

DELIMITER $$

CREATE DEFINER=`davy`@`%` FUNCTION `Split_getCount`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END

获取分割后的字符串的值的函数(根据索引取单个):

DELIMITER $$

CREATE DEFINER=`davy`@`%` FUNCTION `Split_getString`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
  declare result varchar(255) default '';
  set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
  return result;
END

如果要取出所有的话 则要遍历

用法例子如下:

create table IF NOT EXISTS InsertRsAndRi_temp
(
id int(11) auto_increment primary key,
singleVal nvarchar(1000)
);

truncate table  InsertRsAndRi_temp;


 set @count=Split_getCount(splitName_var,'、');

 set @i=1;
        while @i<=@count 
          do  
               set val = Split_getString(splitName_var,'、',i);  
            insert into InsertRsAndRi_temp  set singleVal=val;                     
             
          set i = i+1;  
          end while;

mysql funtion自定义函数返回table

在sql中可以在funtion中直接返回table

但在mysql中不可以  那如果我们要用table怎么办呢 只能在funtion中把值返回回来 再构造一个table 具体用法参考上面Split的函数的 用法例子。

IF(...) begin.... end

改为: IF()  then..elseif ()   then  ..END IF

注意 if else if 与 if elseif的区别！

charindex

查找字符串的位置 在mysql中用locate

将某一字段所有值去重过后拼接为一个字段

sql

dbo.SUMTEXT(distinct t_p_roomusetype_id) 

mysql 

GROUP_CONCAT函数  注意事项 字段为int时要先转换成char

语法:

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
 
1.例如:
SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id; 
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 3,4,5 |
+------------+---------+ 
这 就不需要用php循环了
 $row = $pdo->query("SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id");
 $result = explode(',', $row['courses']); 
 
 
2.当然分隔符还可以自定义，默认是以“,”作为分隔符，若要改为“|||”，则使用SEPARATOR来指定，例如：
SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR '|||') AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 3|||4|||5 |
+------------+---------+ 
 
3.除此之外，还可以对这个组的值来进行排序再连接成字符串，例如按courses_id降序来排：
 SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id;
+------------+---------+ 
| student_id | courses | 
+------------+---------+ 
| 2 | 5,4,3 |
+------------+---------+ 
 
4.需要注意的：
 
a.int字段的连接陷阱

当你用group_concat的时候请注意，连接起来的字段如果是int型，一定要转换成char再拼起来，

否则在你执行后(ExecuteScalar或者其它任何执行SQL返回结果的方法)返回的将不是一个逗号隔开的串，

而是byte[]。

该问题当你在SQLyog等一些工具中是体现不出来的，所以很难发现。

select group_concat(ipaddress) from t_ip 返回逗号隔开的串

select group_concat(id) from t_ip 返回byte[]

select group_concat(CAST(id as char)) from t_dep 返回逗号隔开的串

select group_concat(Convert(id , char)) from t_dep 返回逗号隔开的串

附Cast,convert的用法：

CAST(expr AS type), CONVERT(expr,type) , CONVERT(expr USING transcoding_name)

CAST() 和CONVERT() 函数可用来获取一个类型的值，并产生另一个类型的值。

这个类型 可以是以下值其中的 一个：

BINARY[(N)]

CHAR[(N)]

DATE

DATETIME

DECIMAL

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

b.长度陷阱

用了group_concat后，select里如果使用了limit是不起作用的.

用group_concat连接字段的时候是有长度限制的，并不是有多少连多少。但你可以设置一下。

使用group_concat_max_len系统变量，你可以设置允许的最大长度。

程序中进行这项操作的语法如下，其中 val 是一个无符号整数：

SET [SESSION | GLOBAL] group_concat_max_len = val;

若已经设置了最大长度，则结果被截至这个最大长度。

在SQLyog中执行 SET GLOBAL group_concat_max_len = 10 后，重新打开SQLyog，设置就会生效。

merge ...using...when ...update set...

sql
merge t_p_rooms 
using InsertBdRm_moniter_temp 
on t_p_rooms.t_p_rooms_keyid = InsertBdRm_moniter_temp.t_p_rooms_keyid
when MATCHED and ((t_p_rooms.roomstate in(1,2) or t_p_rooms.roomstate is null) and  (InsertBdRm_moniter_temp.roomstate in (3,4,5,6))) 
then update SET t_p_rooms.roomstate = InsertBdRm_moniter_temp.roomstate,
t_p_rooms.backflg_b = 0,
t_p_rooms.salestate = 1,
t_p_rooms.roomarea = case when InsertBdRm_moniter_temp.roomarea is null then t_p_rooms.roomarea else InsertBdRm_moniter_temp.roomarea end,
t_p_rooms.bakDate_b = null; 

mysql
replace into t_p_rooms (roomstate,backflg_b,alestate,roomarea,bakDate_b) 
select roomstate,0,1,case when InsertBdRm_moniter_temp.roomarea is null then t_p_rooms.roomarea else InsertBdRm_moniter_temp.roomarea end,null from InsertBdRm_moniter_temp 
where t_p_rooms.t_p_rooms_keyid = InsertBdRm_moniter_temp.t_p_rooms_keyid
 and ((t_p_rooms.roomstate in(1,2) or t_p_rooms.roomstate is null) and  (InsertBdRm_moniter_temp.roomstate in (3,4,5,6)));


参考资料：
MySQL中的insert ignore into, replace into等的一些用法总结
http://www.2cto.com/database/201108/99073.html MySQL Replace INTO的使用(转载) 
http://wensong.iteye.com/blog/677919
MsSQL中類似MySQL replace into的指令
http://www.2cto.com/database/201309/240856.html






not unique table/alias tablea
     则 SQL 语句中出现了非唯一的表或别名。
解决方法：
1、请检查出现问题位置的 SQL 语句中是否使用了相同的表名，或是定义了相同的表别名。
2、检查 SELECT 语句中要查询的字段名是不是定义重复，或者没有定义。
检查有没有如下语句:
update tablea innser join tablea on id in (1,2,3) set name='x';