Oracle转MySql常用

1.类型对比

2.常用函数

/* DATE_FORMAT(date,format) 根据format字符串格式化date值 */select date_format('2013-09-21 14:01:20','%Y-%m-%d %H:%i:%s') from dual;select case 2 when 1 then 1 when 2 then 2 else 0 end from dual;select IFNULL(null,0) from dual;

3.分页

从第10条记录开始检索20条记录

select * from sys_option limit 10,20;select * from table [查询条件] order by id limit ?,?;

以下语句是先排序后的分页（很好）

SELECT * FROM test_yutong ORDER BY ID DESC LIMIT 0,1;

order只能这么实现（没有mysql方便）

SELECT * FROM(SELECT * FROM TABLE_A ORDER BY ID DESC)B WHERE ROWNUM=1;

4.自定义序列函数（来源 http://www.oschina.net/code/snippet_110375_12231）

/* CREATE TABLE Sequence */DROP TABLE IF EXISTS Sequence;CREATE TABLE Sequence(  name VARCHAR(32),  maxIndex INTEGER,  CONSTRAINT cst_Sequence_pk PRIMARY KEY (name, maxIndex));/* FUNCTION nextval */SET GLOBAL log_bin_trust_function_creators = 1;DROP FUNCTION IF EXISTS nextval;DELIMITER //CREATE FUNCTION nextval(sequenceName VARCHAR(32))  RETURNS INTEGER  NOT DETERMINISTIC  CONTAINS SQLBEGIN  DECLARE iMaxIndex INTEGER;  UPDATE Sequence SET maxIndex = maxIndex + 1 WHERE name = sequenceName;  SELECT maxIndex INTO iMaxIndex    FROM Sequence WHERE name = sequenceName;  RETURN iMaxIndex;END//DELIMITER ;

/* 测试例子 */INSERT INTO Sequence(NAME,maxIndex)VALUES('CK10_GHDJ',1);SELECT nextval('CK10_GHDJ')FROM DUAL;SELECT * FROM Sequence;