1. 需要的类型转换
2、常用语法与函数对照
3、语法报错与修改
3.1
(m) ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY
(m) ERROR 1221 (HY000): Incorrect usage of UNION and limit
解决：把union all连接的两张表用括号包
如：select xxx limit 1 union all select xxx limit 2
改为：(select xxx limit 1 ) union all ( select xxx limit 2)
3.2
(m) Error : Every derived table must have its own alias
解决：每一个派生出来的表都必须有一个自己的别名
如：select name form (select * from user)
改为：select name form (select * from user) a
3.3
(m) Error : 1093 - You can’t specify target table ‘a’ for update in FROM clause
解决：不能先select出同一表中的某些值，再update这个表(在同一语句中)
如：update testable t set t.age = (select age from testable where id = 1)
改为：update testable t set t.age = (select age from(select age from testable where id = 1) a)
3.4
(m) Incorrect datetime value: ‘’ for function str_to_date
解决：从表中select数据时用到str_to_date(date,format)并不报错; 从表中select数据时用到str_to_date(date,format)并将数据insert到另一张表中报错
如：str_to_date(date,format)
改为：str_to_date (if(date=’’, null , date), format)
3.5
(m) 无rownum
实现，如：
select a.name,a.age ,@rownum:=@rownum+1 as rownum from (select * from user order by age) a ,(select @rownum:=0) t
3.6
(m) 字段内容及列名大小写不敏感的解决方案
解决：创建表时，将字段标记为binary，二进制大小写是敏感的 或 只要在创建表的时候指定collate为utf8_bin，就可以实现大小写敏感，如果建表时未指定，则可修改字段的校对规则，也可以实现大小写敏感。
如：create table test(id int binary);
或 create table test(id int) character set utf8 collate utf8_bin;
或 alter table test modify id collate utf8_bin;
3.7
（m）Mysql 不可使用动态表名
解决：只能使用写死操作，不可使用动态入参
如创建函数不可使用入参：
CREATE FUNCTION SPECIAL_DATE(TODAY VARCHAR(255) , TBALE_NAME VARCHAR(255) ,COL_NAME VARCHAR(255) , SD_TYPE INT) RETURNS int(11)
BEGIN
DECLARE today_tmp VARCHAR(255);
DECLARE num INT;
SELECT COUNT(*) INTO num FROM V_YEAR_LOSS_HISTORYL_DATE WHERE DATE_FORMAT(INSERTTIME,“%Y%m%d”) < TODAY AND DATE_FORMAT(INSERTTIME,“%Y”) = today_tmp;
3.8
临时表的区别：
Oracle和MySQL以不同方式处理临时表。
在MySQL中，临时表是仅对当前用户会话可见的数据库对象，并且一旦会话结束，这些表将自动删除。
Oracle中临时表的定义与MySQL略有不同，因为临时表一旦创建就会存在，直到它们被显式删除，并且对具有适当权限的所有会话都可见。但是，临时表中的数据仅对将数据插入表中的用户会话可见，并且数据可能在事务或用户会话期间持续存在。
3.9
主键的使用：
MySQL：一般使用自动增长类型，在创建表的时候只要指定表的主键为auto increment,插入记录时就不需要再为主键添加记录了，主键会自动增长；
Oracle:没有自动增长，主键一般使用序列，插入记录时将序列号的下一值付给该字段即可，只是ORM框架只是需要native主键生成策略即可。
3.10
长字符串的处理
长字符串的处理ORACLE也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于4000个单字符，
如果要插入更长的字符串，请考虑字段用CLOB类型，方法借用ORACLE里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度值都应该提出警告，返回上次操作。
3.11
模糊查询的比较
MySQL:用字段名like%‘字符串%’
Oracle：也可以用字段名like%'字符串%'但这种方法不能使用索引，速度不快，用字符串比较函数
isnert(字段名，‘字符串’)>0会得到更精确的查找结果，在代码中需要用concat（）方法来拼接，在Oracle迁移需要将代码中这个去除掉。
3.12
空字符串的比较：
MySQL的非空字段也有空的内容，Oracle里定义了非空字段就不容许有空的内容。按MySQL的not null来定义Oracle表结构。
导数据的时候就会产生错误。因此导数据时对空字符进行判断，如果为Null或空字符，需要把它改成一个空格的字符串。
3.13
mysql 全连接和 oracle 全连接查询、区别
oracle的全连接查询可以直接用full on,但是在mysql中没有full join,mysql使用union实现全连接.
oracle的全连接
select * from a full join b on a.id = b.id;
mysql的全连接
select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id;
4、Oracle的函数在MySql中实现
/*创建序列表,用于模拟Oracle序列*/
DROP TABLE IF EXISTS sequence;
CREATE TABLE sequence (
name varchar(128) NOT NULL,
current_value int(10) NOT NULL,
increment int(10) NOT NULL DEFAULT '1',
PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*设置序列初始值*/
DELIMITER ;;
DROP FUNCTION IF EXISTS setval;;
CREATE FUNCTION setval(seq_name VARCHAR(128),val INTEGER) RETURNS INT(11)
BEGIN
UPDATE sequence
SET current_value = val
WHERE NAME = seq_name;
RETURN currval(seq_name);
END ;;
DELIMITER ;
/*获取当前序列*/
DELIMITER ;;
DROP FUNCTION IF EXISTS currval;;
CREATE FUNCTION currval(seq_name VARCHAR(128)) RETURNS INT(11)
BEGIN
DECLARE VALUE INTEGER;
SET VALUE = 0;
SELECT current_value INTO VALUE
FROM sequence
WHERE NAME = seq_name;
RETURN VALUE;
END ;;
DELIMITER ;
--获取下一个序列值
DELIMITER ;;
DROP FUNCTION IF EXISTS nextval;;
CREATE FUNCTION nextval(seq_name VARCHAR(128)) RETURNS INT(11)
BEGIN
UPDATE sequence
SET current_value = current_value + increment
WHERE NAME = LOWER(seq_name);
RETURN currval(LOWER(seq_name));
END ;;
DELIMITER ;
--仿Oracle获取uuid函数sys_guid()
DELIMITER ;;
DROP FUNCTION IF EXISTS sys_guid;;
CREATE FUNCTION sys_guid() RETURNS VARCHAR(60) CHARSET utf8
BEGIN
RETURN REPLACE(UUID(),'-','');
END ;;
DELIMITER ;
--仿Oracle值为空时赋默认值函数:nvl(str,defstr)
DELIMITER ;;
DROP FUNCTION IF EXISTS nvl;;
CREATE FUNCTION nvl(param1 VARCHAR(512),param2 VARCHAR(512)) RETURNS VARCHAR(512) CHARSET utf8
BEGIN
RETURN IF(param1 IS NULL AND param1<>'',param1,param2);
END ;;
DELIMITER ;
--仿Oracle值为空时赋默认值1否则赋默认值2函数:nvl2(str,nulldef,elsedef)
DELIMITER ;;
DROP FUNCTION IF EXISTS nvl2;;
CREATE FUNCTION nvl2(exp1 VARCHAR(512),exp2 VARCHAR(512),exp3 VARCHAR(512)) RETURNS VARCHAR(512) CHARSET utf8
BEGIN
RETURN IF(exp1 IS NULL OR exp1='',exp2,exp3);
END ;;
DELIMITER ;
--仿Oracle字符串转换为数字函数:to_number(str)
DELIMITER ;;
DROP FUNCTION IF EXISTS to_number;;
CREATE FUNCTION to_number(param1 VARCHAR(40)) RETURNS DECIMAL(32,4)
BEGIN
RETURN CONVERT(param1,DECIMAL(32,4));
END;;
DELIMITER ;
--仿Oracle判断某个字符串是否为数字函数:isnumber(str)
DELIMITER ;;
DROP FUNCTION IF EXISTS isnumber;;
CREATE FUNCTION isnumber(c VARCHAR(40)) RETURNS TINYINT(2)
BEGIN
DECLARE v_param INT(10);
DECLARE EXIT HANDLER FOR SQLEXCEPTION RETURN 0;
SET v_param = to_number(c);
RETURN 1;
END ;;
DELIMITER ;
--仿Oracle日期格式转字符串函数:to_char(date,format)
DELIMITER ;;
DROP FUNCTION IF EXISTS to_char;;
CREATE FUNCTION to_char(formatdate DATETIME,formate VARCHAR(40)) RETURNS VARCHAR(40)
BEGIN
DECLARE temp VARCHAR(50);
SET temp = LOWER(formate);
SET temp = REPLACE(temp,'yyyy','%Y');
SET temp = REPLACE(temp,'mm','%m');
SET temp = REPLACE(temp,'dd','%d');
SET temp = REPLACE(temp,'hh24','%H');
SET temp = REPLACE(temp,'mi','%i');
SET temp = REPLACE(temp,'ss','%S');
SET temp = REPLACE(temp,'mon','%c月');
RETURN DATE_FORMAT(formatdate,temp);
END ;;
DELIMITER ;
--仿Oracle日期格式字符串转日期类型函数:to_date(datestr,format)
DELIMITER ;;
DROP FUNCTION IF EXISTS to_date;;
CREATE FUNCTION to_date(formatdate VARCHAR(40),formate VARCHAR(40)) RETURNS VARCHAR(40)
BEGIN
DECLARE temp VARCHAR(50);
SET temp = LOWER(formate);
SET temp = REPLACE(temp,'yyyy','%Y');
SET temp = REPLACE(temp,'mm','%m');
SET temp = REPLACE(temp,'dd','%d');
SET temp = REPLACE(temp,'hh24','%H');
SET temp = REPLACE(temp,'mi','%i');
SET temp = REPLACE(temp,'ss','%S');
RETURN STR_TO_DATE(formatdate,temp);
END ;;
DELIMITER ;
--Oracle的REGEXP_INSTR再mysql中实现
DELIMITER ;;
CREATE FUNCTION regexp_instr(source VARCHAR(4000), pattern VARCHAR(4000), pos INT, occurrence INT, return_option INT, match_type char(1)) RETURNS varchar(4000)
DETERMINISTIC
BEGIN
DECLARE subStr, usePattern VARCHAR(4000);
DECLARE startPos, prevStartPos, startInc, len, lenInc INT;
DECLARE result, src_len, f_count INT default 0;
declare mat_res boolean default false;
if match_type = 'i' then -- 不区分大小写
IF source REGEXP pattern then
set mat_res = true;
end if;
else -- 区分大小写 （其它模式未实现）
IF source REGEXP BINARY pattern then
set mat_res = true;
end if;
end if;
IF mat_res then
set mat_res = false;
set pos = if(pos < 1, 1, pos);
set occurrence = if(occurrence < 1, 1, occurrence);
set source = substring(source, pos);
SET src_len = CHAR_LENGTH(source);
SET usePattern = IF (LEFT(pattern, 1) = '^', pattern, CONCAT('^', pattern));
SET usePattern = IF (RIGHT(pattern, 1) = '$', usePattern, CONCAT(usePattern, '$'));
SET startPos = 1, startInc = 1;
WHILE startPos >= 1 AND startPos <= src_len
AND startPos + 1 - 1 <= src_len
AND !(LEFT(pattern, 1) = '^' AND startPos <> 1)
AND !(RIGHT(pattern, 1) = '$'
AND startPos - 1 < 0) DO
SET len = LEAST(src_len - startPos + 1, src_len), lenInc = -1;
SET prevStartPos = startPos;
lenLoop: WHILE len >= 1 AND len <= src_len
AND startPos + len - 1 <= src_len
AND !(RIGHT(pattern, 1) = '$'
AND startPos + len - 1 <> src_len) DO
SET subStr = SUBSTRING(source, startPos, len);
if match_type = 'i' then
IF subStr REGEXP usePattern then
set mat_res = true;
end if;
else
IF subStr REGEXP BINARY usePattern then
set mat_res = true;
end if;
end if;
IF mat_res then
set mat_res = false;
SET f_count = f_count + 1;
SET startPos = startPos + startInc * len;
LEAVE lenLoop;
END IF;
SET len = len + lenInc;
END WHILE;
IF (occurrence = f_count) then
if (return_option != 0) then
return startPos + pos -1;
end if;
return prevStartPos + pos - 1;
END IF;
IF (startPos = prevStartPos) THEN
SET startPos = startPos + startInc;
END IF;
END WHILE;
ELSE
SET result = 0;
END IF;
RETURN result;
END;;
DELIMITER ;
--mysql自定义函数实现，自定义oracle中decode方法
--仿Oracle中的decode函数(低级版)
DELIMITER ;;
CREATE FUNCTION decode_oracle(pattern VARCHAR(255),string_a VARCHAR(255),string_b VARCHAR(255),string_c VARCHAR(255)) RETURNS varchar(255)
BEGIN
DECLARE result VARCHAR(255) DEFAULT "";
#正常对比函数
if pattern=string_a then
set result = string_b;
ELSEIF pattern=string_b then
set result = string_c;
ELSEIF pattern=string_c then
set result = string_c;
#处理空值
ELSEIF string_a is null then
if pattern is not null then
set result = string_c;
ELSE
set result = string_b;
end if;
ELSEIF string_b is null then
set result = string_c;
ELSEIF string_c is null then
set result = string_c;
ELSE
set result = string_c;
END IF;
RETURN result;
END;;
DELIMITER ;
--MySQL实现Oracle的trunc(date)函数代替
-- 返回当年第一天
oracle> SELECT trunc(sysdate, 'yyyy') FROM dual;
oracle> SELECT trunc(sysdate, 'yy') FROM dual;
mysql> SELECT str_to_date(date_format(now(), '%Y0101'), '%Y%m%d');
-- 返回当月第一天
oracle> SELECT trunc(sysdate, 'mm') FROM dual;
mysql> SELECT str_to_date(date_format(now(), '%Y%m01'), '%Y%m%d');
-- 返回当前年月日
oracle> SELECT trunc(sysdate, 'dd') FROM dual;
mysql> SELECT str_to_date(date_format(now(), '%Y%m%d'), '%Y%m%d');
-- 返回当前星期的第一天(星期日)
oracle> SELECT trunc(sysdate, 'd') FROM dual;
mysql> select case when WEEKDAY(now()) = 6
then str_to_date(date_format(now(), '%Y%m%d'), '%Y%m%d')
else str_to_date(date_format(date_sub(now(),INTERVAL WEEKDAY(now()) + 1 DAY), '%Y%m%d'), '%Y%m%d') end;
-- 返回当前日期截取到小时,分秒补0
oracle> SELECT trunc(sysdate, 'hh') FROM dual;
mysql> SELECT str_to_date(date_format(now(), '%Y%m%d%H'), '%Y%m%d%H');
-- 返回当前日期截取到分,秒补0
oracle> SELECT trunc(sysdate, 'mi') FROM dual;
mysql> SELECT str_to_date(date_format(now(), '%Y%m%d%H%i'), '%Y%m%d%H%i');
706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
