Oracle to MySQL Common

1. Type comparison

Oracle	Mysql
Int	Int
Number (6,2)	Decimal (6,2)
Date (sysdate)	Timestamp (now ())
VARCHAR2 (20)	varchar (20)

2. Common functions

/* DATE_FORMAT (Date,format) formats the DATE value according to the format string */select date_format (' 2013-09-21 14:01:20 ', '%y-%m-%d%h:%i:%s ') from Dual;select Case 2 While 1 then 1 while 2 then 2 else 0 end from Dual;select ifnull (null,0) from dual;

3. Paging

Retrieve 20 records starting from 10th record

SELECT * FROM Sys_option limit 10,20;select * FROM table [query condition] ORDER by ID limit?,?;

The following statements are sorted after pagination (very good)

SELECT * from Test_yutong ORDER by ID DESC LIMIT 0, 1;

Order can only be implemented this way (no MySQL is convenient)

SELECT * FROM (SELECT * to Table_a ORDER by ID DESC) B WHERE rownum=1;

4. Custom sequence function (source http://www.oschina.net/code/snippet_110375_12231)

/* create table sequence */drop table if exists sequence; Create table sequence (  name varchar (+),  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 ; 

/* Test example */insert into Sequence (name,maxindex) VALUES (' Ck10_ghdj ', 1); SELECT nextval (' Ck10_ghdj ') from DUAL; SELECT * from Sequence;

This article is from my Tech blog blog, so be sure to keep this source http://programmer.blog.51cto.com/2859493/1564370

Oracle to MySQL Common