将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

原文链接:

mysql full join 实现方式（转）

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存储过程中用变量做表名

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 会失败！

参考链接:

http://www.cnblogs.com/trying/p/3296793.html

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 utf8BEGIN  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，设置就会生效。

原文链接:

关于mysql函数GROUP_CONCAT

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';