Oracle到mysql转换的问题总结

2024年1月26日发(作者：)

Oracle到mysql转换的问题总结

常用字段类型区别

oracle

number（10,0）

number（10,2）

varchar2

date

Colb

mysql

int

decimal（10,2）

varchar

datetime

text

个别语句写法区别

里只可以用单引号包起字符串，mysql里可以用双引号和单引号。

在select * from () ....，from后面是一个结果集时，括号后面必须加上别名。

在delete数据时不能给表加别名，如：delete from table1 ，会报错，但是可以这样写：delete T from table1 。

不支持在同一个表中先查这个表在更新这个表，举个例子说明一下，

insert into table1 values（字段1，（select 字段2 from ）），

但是可以在后面那个table1加上别名就没有问题了。

insert into table1 values（字段1，（select T.字段2 from table1 ））

有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。也可以自定义函数实现oracle的nextval。

6.翻页的sql语句处理，MYSQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数，例如：select * from table limit m,n，意思是从m+1开始取n条。

常见的函数替换

1.日期转换方面的函数

oracle mysql 说明

to_char(date,’yyyy-MM-dd

hh24:mi:ss’)

to_date(str,’yyyy-MM-dd

hh24:mi:ss’)

to_date(str,’yyyy-MM-dd

hh24:mi:ss’)

to_char()、to_number()

date + n

date_format(date,'%Y-%m-%d %H:%i:%s')

str_to_date(str,'%Y-%m-%d %H:%i:%s')

str_to_date(str,'%Y-%m-%d%T')

convert(字段名,类型)

date_add(date,interval n

day)

注意时间格式的对应

注意时间格式

注意时间格式

类型转换

日期增加n天

select

date_add(sysdate(),INTERVAL 2 DAY);

add_months(date,n) date_add(date,interval n 日期增加n个月

month) select

date_add(sysdate(),INTERVAL 2 MONTH);

date1 - date2 datediff(date1,date2) 日期相减获取天数

中decode（）函数，可以用case when进行替换

例子：

Oracle：select decode(a,b,c,d) as col1 from table1;

Mysql：select

case

when a=b then c

when a!=b then d

end as col1

from table1

的函数 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY

col2)，根据col1分组，在分组内根据col2排序，改函数计算的值就表示每组内部排序后的顺序编号。

Mysql没有这个函数，可以用mysql的用户变量来实现

例子：

Oracle：select row_number() over(partition by col1 order by col2) as num from table1

Mysql：select num1 as num from (select if(@pdept=col1,@rank:=@rank+1,@rank:=1)

as num1,@pdept:=col1 from table1 order by col2 ) H

这里用到mysql的用户变量。

的行号ROWNUM，mysql没有这个行号，也需要用用户变量来实现。

例子：

Oracle：select * from table1 where rownum - col1=0

Mysql: select * from table1,(SELECT (@rowNum := 0)) HH where

(@rowNum := @rowNum + 1) - col1=0

中的 substr （hello,a,b） mysql中是substring（hello,a,b） oracle的a=0和a=1是一样的，都是从第一个开始。Mysql是从0开始。

的nvl()对应mysql的ifnull()。

中的_CONCAT（），列转行函数，以逗号隔开，mysql可以用GROUP_CONCAT()进行替换。

的||可以用mysql的+替代，但是mysql在往某列加上字符时应该用concat，

例如给表中name字段加上x：update table1 set name=concat（x，name）。

1.1 移植过程中重点问题

1.1.1 数据类型差异

ORACLE数据库和MYSQL数据库在数据类型方面差异比较大，而且数据类型也是一个数据库存储数据的基础，所以找到数据类型之间的对应是整个系统进行移植的基础。以下给出了ORACLE à MYSQL数据类型的对应关系。

数值类型：

NUMBER à DECIMAL，精度刻度都不变

注：如果是序列用BIGINT

字符串类型：

VARCHAR2 à VARCHAR长度不变。

LONG à LONGTEXT

这里有可能遇到的问题是超过主键key长度的问题，根据实际情况适当修改，如果是TEXT类型也需要指名长度，否则建立key会报错

日期类型：

DATE à DATETIME

TIMESTAMP(N) à TIMESTAMP

1.1.2 SQL语法差异

SEQUENCE：

MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：

1、 作为表中自增字段的序列号。

2、 程序中获得自动编号。

MYSQL数据类型中存在 AUTO_INCREMENT为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能。

1、 对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现。

2、 ORACLE开发的应用程序中直接SELECT SEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现。

首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 3 |

+------------------+

1 row in set (0.06 sec)

我们可以创建一个含有自增列的表，对该表进行INSERT操作后，再利用LAST_INSERT_ID()函数来获得刚刚INSERT的值，也就是相当于ORACLE中的SEQUENCE. NETVAL。也就是INSERT操作+SELECT操作获得一个自动编号。

mysql> CREATE TABLE MOCHA_BE_SEQUENCE(ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREM

ENT);

Query OK, 0 rows affected (0.63 sec)

mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);

Query OK, 1 row affected (0.09 sec)

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 1 |

+------------------+

1 row in set (0.05 sec)

mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);

Query OK, 1 row affected (0.06 sec)

mysql> SELECT LAST_INSERT_ID();

+------------------+

| LAST_INSERT_ID() |

+------------------+

| 2 |

+------------------+

1 row in set (0.00 sec)

BLOG：

ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。

视图

Mysql视图限制（1）SELECT语句不能包含FROM子句中的子查询。

（2）SELECT语句不能引用系统或用户变量。

（3）SELECT语句不能引用预处理语句参数。

（4）在存储子程序内，定义不能引用子程序参数或局部变量。

（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。

（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

（7）在视图定义中命名的表必须已存在。

（8）不能将触发程序与视图关联在一起。

我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。

例子：

Oracle中带子查询的视图：

CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS

SELECT USER_ID AS ID, USER_NAME AS NAME, CONCAT(CONCAT(CONCAT(NVL(_CODE,'00000001.10000000'), '.00.'), PERSON__LEVEL), LPAD(PERSON__PERSON_NO,9,'0')) AS CODE, NVL(_LEVEL, 1) AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM (SELECT _ID , _NAME, NVL(_ID, -1) AS ORG_ID, NVL(_PERSON_NO, 0) AS ORG_PERSON_NO, NVL(_LEVEL, '64') AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON WHERE _ID = _ID (+) AND _FLAG = '0' And ='A') PERSON_POSITION, MOCHA_IM_ORG_VIEW ORG

WHERE PERSON__ID = _ID (+)

UNION ALL

SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM MOCHA_IM_PERSON PERSON WHERE _FLAG = '0' And ='I'

UNION ALL

SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE

,1 AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A'

UNION ALL

SELECT LPAD(ORG_ID,20,' ') AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW

UNION ALL

SELECT '-1' AS ID, '未分派人员' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE

FROM DUAL

UNION ALL

SELECT '-2' AS ID, '待删除人员' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE

FROM DUAL

UNION ALL

SELECT '-3' AS ID, '系统管理员' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL

/

转为mysql：

CREATE OR REPLACE VIEW PERSON_POSITION AS

SELECT _ID , _NAME, IFNULL(_ID, -1) AS ORG_ID, IFNULL(_PERSON_NO, 0) AS ORG_PERSON_NO, IFNULL(_LEVEL, '64') AS PERSON_LEVEL

FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN MOCHA_IM_PERSON PERSON ON _ID = _ID

WHERE _FLAG = '0' And ='A';

/

CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS

SELECT USER_ID AS ID, USER_NAME AS NAME,

CONCAT(CONCAT(CONCAT(IFNULL(_CODE,'00000001.10000000'), '.00.'),

PERSON__LEVEL), LPAD(PERSON__PERSON_NO,9,'0')) AS CODE,

IFNULL(_LEVEL, 1) AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM MOCHA_IM_ORG_VIEW ORG LEFT JOIN PERSON_POSITION ON PERSON__ID = _ID

UNION ALL

SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM MOCHA_IM_PERSON PERSON WHERE _FLAG = '0' And ='I'

UNION ALL

SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE

,1 AS REC_LEVEL, 'PERSON' AS REC_TYPE

FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A'

UNION ALL

SELECT LPAD(ORG_ID,20,' ') AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW

UNION ALL

SELECT '-1' AS ID, '未分派人员' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE

FROM DUAL

UNION ALL

SELECT '-2' AS ID, '待删除人员' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE

FROM DUAL

UNION ALL

SELECT '-3' AS ID, '系统管理员' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL

/

触发器，函数，存储过程

语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql控制流程等等。

对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。就是说mysql不支持oracle中create trigger *** before insert or

Update or delete on ***.此时应该把这个触发器拆分为3个触发器。

例子：

Oracle下触发器：

CREATE OR REPLACE TRIGGER MOCHA_IM_DUTY_TRG

BEFORE INSERT OR UPDATE OR DELETE ON MOCHA_IM_DUTY

FOR EACH ROW

DECLARE

befImg VARCHAR2(2000);

afterImg VARCHAR2(2000);

action VARCHAR2(10);

action_module VARCHAR2(10);

info_category VARCHAR2(20);

BEGIN

action_module := 'IM';

info_category := 'duty';

befImg := '[DUTY_ID]' || :_id || '[DUTY_NAME]' || :_name;

afterImg := '[DUTY_ID]' || :_id || '[DUTY_NAME]' || :_name;

IF (:_id is null) THEN

action := 'Insert';

befImg := '';

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);

ELSIF (:_id is null) THEN

action := 'Delete';

afterImg := '';

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);

ELSE

action := 'Update';

IF(befImg != afterImg) THEN

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (SYSDATE, action, action_module, info_category, befImg, afterImg);

END IF;

END IF;

EXCEPTION

WHEN OTHERS then

befImg := '';

end;

/

mysql下：

CREATE TRIGGER MOCHA_IM_DUTY_TRG_INSERT

BEFORE INSERT ON MOCHA_IM_DUTY

FOR EACH ROW

BEGIN

DECLARE befImg VARCHAR(2000);

DECLARE afterImg VARCHAR(2000);

DECLARE action VARCHAR(10);

DECLARE action_module VARCHAR(10);

DECLARE info_category VARCHAR(20);

DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND

begin

set befImg = '';

end;

set action_module = 'IM';

set info_category = 'duty';

set afterImg =CONCAT( '[DUTY_ID]' , _id , '[DUTY_NAME]' , _name);

set action = 'Insert';

set befImg = '';

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);

end;

/

CREATE TRIGGER MOCHA_IM_DUTY_TRG_DEL

BEFORE DELETE ON MOCHA_IM_DUTY

FOR EACH ROW

BEGIN

DECLARE befImg VARCHAR(2000);

DECLARE afterImg VARCHAR(2000);

DECLARE action VARCHAR(10);

DECLARE action_module VARCHAR(10);

DECLARE info_category VARCHAR(20);

DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND

begin

set befImg = '';

end;

set action_module = 'IM';

set info_category = 'duty';

set befImg = CONCAT('[DUTY_ID]' , _id , '[DUTY_NAME]' , _name);

set action = 'Delete';

set afterImg = '';

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);

end;

/

CREATE TRIGGER MOCHA_IM_DUTY_TRG_UPDATE

BEFORE UPDATE ON MOCHA_IM_DUTY

FOR EACH ROW

BEGIN

DECLARE befImg VARCHAR(2000);

DECLARE afterImg VARCHAR(2000);

DECLARE action VARCHAR(10);

DECLARE action_module VARCHAR(10);

DECLARE info_category VARCHAR(20);

DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND

begin

set befImg = '';

end;

set action_module = 'IM';

set info_category = 'duty';

set befImg = CONCAT('[DUTY_ID]' , _id , '[DUTY_NAME]' , _name);

set afterImg =CONCAT( '[DUTY_ID]' , _id , '[DUTY_NAME]' , _name);

IF (_id is null) THEN

set action = 'Insert';

set befImg = '';

ELSEIF (_id is null) THEN

set action = 'Delete';

set afterImg = '';

ELSE

set action = 'Update';

IF(befImg != afterImg) THEN

INSERT INTO MOCHA_IM_AUDIT_LOG (ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE) VALUES (now(), action, action_module, info_category, befImg, afterImg);

END IF;

END IF;

end;

/

在声过程和函数时注意过程支持inout参数函数不支持，mysql下函数是returns

Oracle下过程：

create or replace procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR2)

IS

begin

execute immediate ' insert into MOCHA_FE_DOC_BODY_TEMP '||v_sql;

end MOCHA_FE_OPEN_DOC_SP;

/

Mysql下：

create procedure MOCHA_FE_OPEN_DOC_SP(v_sql VARCHAR(200))

begin

set @sqltext=CONCAT( ' insert into MOCHA_FE_DOC_BODY_TEMP ',v_sql);

prepare stmt from @sqltext;

execute stmt;

end ;

/

Oracle下函数：

CREATE OR REPLACE FUNCTION GET_PER_NAME_LEVEL(

v_UserId VARCHAR2)

RETURN VARCHAR2 IS

v_UserName VARCHAR2(30);

v_PrimaryPosition VARCHAR2(1);

v_PersonLevel VARCHAR2(2);

v_LevelName VARCHAR2(4000);

v_Result VARCHAR2(4000);

v_RecCount NUMBER;

CURSOR c_PersonPosition IS

SELECT _NAME, _LEVEL, Y_POSITION, PERSON__NAME

FROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVEL

WHERE _ID = _ID AND _LEVEL = PERSON__LEVEL AND _ID = v_UserId

ORDER BY Y_POSITION DESC;

BEGIN

SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId;

IF v_RecCount=0 THEN

SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON

WHERE USER_ID = v_UserId;

ELSE

OPEN c_PersonPosition;

LOOP

FETCH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;

EXIT WHEN c_PersonPosition%NOTFOUND;

v_Result := NVL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), '['));

IF (v_PrimaryPosition = '1' AND (v_RecCount >1 OR SUBSTR(v_PersonLevel,2) < '3')) THEN

v_Result := CONCAT(v_Result, v_LevelName);

ELSIF (v_RecCount >0 AND v_PrimaryPosition = '0') THEN

v_Result := CONCAT(CONCAT(v_Result, '兼'), v_LevelName);

END IF;

END LOOP;

CLOSE c_PersonPosition;

IF LENGTH(v_Result)>0 THEN

v_Result := CONCAT(v_Result, ']');

END IF;

IF SUBSTR(v_Result, LENGTH(v_Result)-1) = '[]' THEN

v_Result := SUBSTR(v_Result,1,LENGTH(v_Result)-2);

END IF;

END IF;

RETURN v_Result;

END;

Mysql下函数：

CREATE FUNCTION GET_PER_NAME_LEVEL(v_UserId VARCHAR(30))

RETURNS VARCHAR(30)

BEGIN

DECLARE v_UserName VARCHAR(30);

DECLARE v_PrimaryPosition VARCHAR(1);

DECLARE v_PersonLevel VARCHAR(2);

DECLARE v_LevelName VARCHAR(4000);

DECLARE v_Result VARCHAR(4000);

DECLARE v_RecCount DECIMAL;

DECLARE c_PersonPosition CURSOR FOR

SELECT _NAME, _LEVEL, Y_POSITION, PERSON__NAME

FROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVEL

WHERE _ID = _ID AND _LEVEL = PERSON__LEVEL AND _ID = v_UserId

ORDER BY Y_POSITION DESC;

SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId;

IF v_RecCount=0 THEN

SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON

WHERE USER_ID = v_UserId;

ELSE

OPEN c_PersonPosition;

REPEAT

FETCH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;

IF NOT done THEN

set v_Result = IFNULL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), '['));

IF (v_PrimaryPosition = '1' AND (v_RecCount >1 OR SUBSTR(v_PersonLevel,2) < '3')) THEN

set v_Result = CONCAT(v_Result, v_LevelName);

ELSEIF (v_RecCount >0 AND v_PrimaryPosition = '0') THEN

set v_Result = CONCAT(CONCAT(v_Result, '兼'), v_LevelName);

END IF;

END IF;

UNTIL done END REPEAT;

CLOSE c_PersonPosition;

IF LENGTH(v_Result)>0 THEN

set v_Result = CONCAT(v_Result, ']');

END IF;

IF SUBSTR(v_Result, LENGTH(v_Result)-1) = '[]' THEN

set v_Result = SUBSTR(v_Result,1,LENGTH(v_Result)-2);

END IF;

END IF;

RETURN v_Result;

END；

/

索引

普通索引，唯一索引，全文索引都支持，但是不支持bitmap索引。

其他：

Oracle用||连接字符串，mysql不支持||，但可以用CONCAT来连接，nvl在mysql中是ifnull，instr函数oracle与mysql有些不同，oracle支持的参数比mysql多，功能更强大，但是mysql有substring_index配合substring，length可以做绝大多数的字符的操作

为了支持中文，字符集选择utf8，为了支持事务和行级锁选择存储引擎为InnoDB

这些可以在mysql的配置文件里去改，也可以在建表的时候写进sql中

最后mysql与oracle的语法（临时表，表的comment，定义变量啊等等）函数上的区别还有很多。

MySQL转换Oracle的七大注意事项

有很多应用项目, 刚起步的时候用MySQL数据库基本上能实现各种功能需求，随着应用用户的增多，数据量的增加，MySQL渐渐地出现不堪重负的情况：连接很慢甚至宕机，于是就有MySQL转换Oracle的需求，应用程序也要相应做一些修改。下面总结出MySQL转换Oracle的几点注意事项，希望对大家有所帮助。

1．自动增长的数据类型处理

MySQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。Oracle没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。

CREATE SEQUENCE 序列号的名称 (最好是表名 序列号标记) INCREMENT BY 1 START WITH

1 MAXVALUE 99999 CYCLE NOCACHE；

其中最大的值按字段的长度来定, 如果定义的自动增长的序列号 NUMBER(6) , 最大值为999999 INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

2. 单引号的处理

MySQL里可以用双引号包起字符串，Oracle里只可以用单引号包起字符串。在插入和修改字符串前必须做单引号的替换：把所有出现的一个单引号替换成两个单引号。

3. 翻页的SQL语句的处理

MySQL处理翻页的SQL语句比较简单，用LIMIT 开始位置, 记录个数；PHP里还可以用SEEK定位到结果集的位置。Oracle处理翻页的SQL语句就比较繁琐了。每个结果集只有一个ROWNUM字段标明它的位置, 并且只能用ROWNUM＆lt;100, 不能用ROWNUM>80。 以下是经过分析后较好的两种Oracle翻页SQL语句( ID是唯一关键字的字段名 )：

语句一：

1.

2.

3.

4.

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE

ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW,

ID FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2)

WHERE NUMROW > 80 AND NUMROW ＆lt; 100 ) ORDER BY 条件3;

语句二：

1.

2.

3.

4.

SELECT * FROM (( SELECT ROWNUM AS NUMROW,

c.* from (select [FIELD_NAME,...]

FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c)

WHERE NUMROW > 80 AND NUMROW ＆lt; 100 ) ORDER BY 条件3;

4． 长字符串的处理

长字符串的处理Oracle也有它特殊的地方。INSERT和UPDATE时最大可操作的字符串长度小于等于 4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型，方法

借用Oracle里自带的DBMS_LOB程序包。插入修改记录前一定要做进行非空和长度判断，不能为空的字段值和超出长度字段值都应该提出警告, 返回上次操作。

5. 日期字段的处理

MySQL日期字段分DATE和TIME两种，Oracle日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) 年-月-日 24小时:分钟:秒 的格式YYYY-MM-DD

HH24:MI:SS TO_DATE()

日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’)

日期字段的数学运算公式有很大的不同。MySQL找到离当前时间7天用 DATE_FIELD_NAME >

SUBDATE（（NOW（），INTERVAL 7 DAY）Oracle找到离当前时间7天用

DATE_FIELD_NAME >SYSDATE - 7；

6. 空字符的处理

MySQL的非空字段也有空的内容，Oracle里定义了非空字段就不容许有空的内容。 按MySQL的NOT NULL来定义Oracle表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

7. 字符串的模糊比较

MySQL里用 字段名 likeOracle里也可以用 字段名 like 但这种方法不能使用索引,

速度不快用字符串比较函数 instr(字段名,字符串)>0 会得到更精确的查找结果 8. 程序和函数里，操作数据库的工作完成后请注意结果集和指针的释放。

以上MySQL转换Oracle的相关注意事项，供您参考。

序列处理

MySQL自增长与Oracle序列的区别:

自增长只能用于表中的其中一个字段

自增长只能被分配给固定表的固定的某一字段，不能被多个表共用.

自增长会把一个未指定或NULL值的字段自动填上.

摘自：/blog/sequences-in-mysql-2006-01-26/

•

•

•

经过初步测试，需要进行大数据量的测试

在mysql中实现：

NEXTVAL(‘sequence’);

CURRVAL(‘sequence’);

SETVAL(‘sequence’,value);

currval

DROP TABLE IF EXISTS sequence;

CREATE TABLE sequence (

name VARCHAR(50) NOT NULL,

current_value INT NOT NULL,

increment INT NOT NULL DEFAULT 1,

PRIMARY KEY (name)

);

INSERT INTO sequence VALUES ('MovieSeq',3,5);

DROP FUNCTION IF EXISTS currval;

DELIMITER $

CREATE FUNCTION currval (seq_name VARCHAR(50))

RETURNS INTEGER

CONTAINS SQL

BEGIN

DECLARE value INTEGER;

SET value = 0;

SELECT current_value INTO value

FROM sequence

WHERE name = seq_name;

RETURN value;

END$

DELIMITER ;

mysql> INSERT INTO sequence VALUES ('MovieSeq',3,5);

Query OK, 1 row affected (0.00 sec)

mysql> select currval('MovieSeq');

+---------------------+

| currval('MovieSeq') |

+---------------------+

| 3 |

+---------------------+

1 row in set (0.00 sec)

mysql> INSERT INTO sequence VALUES ('wangxjd',3,5);

Query OK, 1 row affected (0.00 sec)

mysql> select * from sequence;

+----------+---------------+-----------+

| name | current_value | increment |

+----------+---------------+-----------+

| MovieSeq | 3 | 5 |

| wangxjd | 3 | 5 |

+----------+---------------+-----------+

2 rows in set (0.00 sec)

nextval

DROP FUNCTION IF EXISTS nextval;

DELIMITER $

CREATE FUNCTION nextval (seq_name VARCHAR(50))

RETURNS INTEGER

CONTAINS SQL

BEGIN

UPDATE sequence

SET current_value = current_value + increment

WHERE name = seq_name;

RETURN currval(seq_name);

END$

DELIMITER ;

mysql> select nextval('MovieSeq');

+---------------------+

| nextval('MovieSeq') |

+---------------------+

| 8 |

+---------------------+

1 row in set (0.00 sec)

mysql> select nextval('wangxjd');

+--------------------+

| nextval('wangxjd') |

+--------------------+

| 8 |

+--------------------+

1 row in set (0.00 sec)

mysql> select nextval('wangxjd');

+--------------------+

| nextval('wangxjd') |

+--------------------+

| 13 |

+--------------------+

1 row in set (0.00 sec)

setval

DROP FUNCTION IF EXISTS setval;

DELIMITER $

CREATE FUNCTION setval (seq_name VARCHAR(50), value INTEGER)

RETURNS INTEGER

CONTAINS SQL

BEGIN

UPDATE sequence

SET current_value = value

WHERE name = seq_name;

RETURN currval(seq_name);

END$

DELIMITER ;

mysql> select setval('wangxjd',500);

+-----------------------+

| setval('wangxjd',500) |

+-----------------------+

| 500 |

+-----------------------+

1 row in set (0.00 sec)

mysql> select * from sequence;

+----------+---------------+-----------+

| name | current_value | increment |

+----------+---------------+-----------+

| MovieSeq | 8 | 5 |

| wangxjd | 500 | 5 |

+----------+---------------+-----------+

2 rows in set (0.00 sec)

个人理解：

对表sequence中column name的取值可以定义到静态文件中，在表sequence中插入一条数据后，执行INSERT INTO sequence VALUES ('wangxjd',3,5) 对序列进行初步赋值。

为避免多个主机同时查询，可以根据主机不同设定不同的name值和不同的increment的值，可以是1,2，相同的increment值【2】，这样在两台主机上取值的时候，一个主机取出的值为1,3,5.7.9………………..两外一条主机获取值为2,4,6,8，10…………避免重复。

Name

Seq0

Seq1

Seq2

Seq3

Seq4

Seq5

Seq6

Seq7

Seq8

Seq9

current_value

0

1

2

3

4

5

6

7

8

9

increment

10

10

10

10

10

10

10

10

10

10

Mysql中文乱码问题完美解决方案

总结：

在建立database、create table和insert语句时候，使用统一的编码UTF8.

--创建数据库

mysql> create database asd character set 'utf8' collate 'utf8_general_ci';

Query OK, 1 row affected (0.00 sec)

--建表：

create table TD_PTL_LOVEMUSIC

(

ID INT not null,

BILLBOARDNAME VARCHAR(100),

BILLBOARDCODE VARCHAR(100),

MUSICBOXNAME VARCHAR(100),

MUSICBOXADDR VARCHAR(100),

MUSICBOXPRICE VARCHAR(20),

MUSICNAME VARCHAR(100),

MUSICADDR VARCHAR(100),

ACTORNAME VARCHAR(100),

ACTORADDR VARCHAR(100),

CREATE_DATE DATETIME,

REMARK VARCHAR(1000)

)DEFAULT CHARSET=utf8；

-插入语句

set names utf8;

insert into TS_DATATYPE (TYPE_ID, TYPE_NAME, TYPE_ALIAS, STATE, NOTE)

values (1, '产品类型', 'PRODTYPE', 1, '产品类型表');

用show variables like “%colla%”；show varables like “%char%”；这两条命令查看数据库与服务端的字符集设置

MySQL会出现中文乱码的原因不外乎下列几点：

本身设定问题，例如还停留在latin1

的语系设定问题(包含character与collation)

3.客户端程式(例如php)的连线语系设定问题

强烈建议使用utf8

utf8可以兼容世界上所有字符

一、避免创建数据库及表出现中文乱码和查看编码方法

1、创建数据库的时候：CREATE DATABASE `test`

CHARACTER SET 'utf8'

COLLATE 'utf8_general_ci';

2、建表的时候 CREATE TABLE `database_user` (

`ID` varchar(40) NOT NULL default '',

`UserID` varchar(40) NOT NULL default '',

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这3个设置好了，基本就不会出问题了,即建库和建表时都使用相同的编码格式。

但是如果你已经建了库和表可以通过以下方式进行查询。

1.查看默认的编码格式:

mysql> show variables like "%char%";

+--------------------------+---------------+

| Variable_name | Value |

+--------------------------+---------------+

| character_set_client | gbk |

| character_set_connection | gbk |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | gbk |

| character_set_server | utf8 |

| character_set_system | utf8 |

+--------------------------+-------------+

注：以前2个来确定,可以使用set names utf8,set names gbk设置默认的编码格式;

执行SET NAMES utf8的效果等同于同时设定如下：

SET character_set_client='utf8';

SET character_set_connection='utf8';

SET character_set_results='utf8';

2.查看test数据库的编码格式:

mysql> show create database test;

+------------+------------------------------------------------------------------------------------------------+

| Database | Create Database |

+------------+------------------------------------------------------------------------------------------------+

| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |

+------------+------------------------------------------------------------------------------------------------+

3.查看yjdb数据库的编码格式:

mysql> show create table yjdb;

| yjdb | CREATE TABLE `yjdb` (

`sn` int(5) NOT NULL AUTO_INCREMENT,

`type` varchar(10) NOT NULL,

`brc` varchar(6) NOT NULL,

`teller` int(6) NOT NULL,

`telname` varchar(10) NOT NULL,

`date` int(10) NOT NULL,

`count` int(6) NOT NULL,

`back` int(10) NOT NULL,

PRIMARY KEY (`sn`),

UNIQUE KEY `sn` (`sn`),

UNIQUE KEY `sn_2` (`sn`)

) ENGINE=MyISAM AUTO_INCREMENT=1826 DEFAULT CHARSET=gbk ROW_FORMAT=DYNAMIC |

二、避免导入数据有中文乱码的问题

1:将数据编码格式保存为utf-8

设置默认编码为utf8：

set names utf8;

设置数据库db_name默认为utf8:

ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

设置表tb_name默认编码为utf8:

ALTER TABLE `tb_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

导入：

LOAD DATA LOCAL INFILE 'C:' INTO TABLE yjdb;

2:将数据编码格式保存为ansi(即GBK或GB2312)

设置默认编码为gbk：

set names gbk;

设置数据库db_name默认编码为gbk:

ALTER DATABASE `db_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

设置表tb_name默认编码为gbk:

ALTER TABLE `tb_name` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

导入：

LOAD DATA LOCAL INFILE 'C:' INTO TABLE yjdb;

注：8不要导入gbk，gbk不要导入UTF8;

下不支持UTF8的显示;

三、解决网页中乱码的问题

将网站编码设为 utf-8,这样可以兼容世界上所有字符。

如果网站已经运作了好久,已有很多旧数据,不能再更改简体中文的设定,那么建议将页面的编码设为 GBK, GBK与GB2312的区别就在于:GBK能比GB2312显示更多的字符,要显示简体码的繁体字,就只能用GBK。

1.编辑/etc/ ,在[mysql]段加入default_character_set=utf8;

2.在编写Connection URL时，加上?useUnicode=true&characterEncoding=utf-8参;

3.在网页代码中加上一个"set names utf8"或者"set names gbk"的指令，告诉MySQL连线内容都要使用

utf8或者gbk;

mysql日期和字符相互转换

对应原有的TIMESTAMP【对应oracle的to_timestamp】，字符串转换为日期类型的都可采用以下案例：

str_to_date('08-11-2011 11:11:23', '%d-%m-%Y %T')

mysql日期和字符相互转换

date_format(date,'%Y-%m-%d')

-------------->oracle中的to_char();

str_to_date(date,'%Y-%m-%d')

-------------->oracle中的to_date();

%Y：代表4位的年份

%y：代表2为的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)

%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)

%k：代表 小时,格式为(0……23)

%h： 代表小时,格式为(01……12)

%I： 代表小时,格式为(01……12)

%l ：代表小时,格式为(1……12)

%i： 代表分钟, 格式为(00……59)

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表 时间,格式为24 小时(hh:mm:ss)

常用脚本

时间类型default转换：

CREATE_DATE DATE default SYSDATE,

转换成

CREATE_DATE timestamp default now();

启动mysql

启动：net start mySql;

进入：mysql -u root -p/mysql -h localhost -u root -p databaseName;

列出数据库：show databases;

选择数据库：use databaseName;

列出表格：show tables；

显示表格列的属性：show columns from tableName；

建立数据库：source ;

匹配字符：可以用通配符_代表任何一个字符，％代表任何字符串;

增加一个字段：alter table tabelName add column fieldName dateType;

增加多个字段：alter table tabelName add column fieldName1 dateType,add columns

fieldName2 dateType;

多行命令输入:注意不能将单词断开;当插入或更改数据时，不能将字段的字符串展开到多行里，否则硬回车将被储存到数据中;

增加一个管理员帐户：grant all on *.* to user@localhost identified by "password";

每条语句输入完毕后要在末尾填加分号';'，或者填加'g'也可以；

查询时间：select now();

查询当前用户：select user();

查询数据库版本：select version();

查询当前使用的数据库：select database();

表分区处理：

--分区 对datetime，TIMESTAMP 处理

--对int类型 less than 即可。partition p0 values less than (10)；

create table TW_PTL_ACCESSLOG

(

LOGIN_ACCEPT VARCHAR(16) not null,

USER_IP VARCHAR(128),

SESSIONID VARCHAR(128) not null,

USER_ID VARCHAR(500),

LOGIN_TYPE INT(2),

LOGIN_NUM VARCHAR(20),

URL VARCHAR(1000),

SRC_URL VARCHAR(1000),

ACCESS_BEGIN_DATE TIMESTAMP not null,

ACCESS_USED_TIME INT(6),

ACCESS_FLAG CHAR(1) default 'Y',

CREATE_DATE timestamp default now(),

CREATE_BY VARCHAR(20),

NOTE VARCHAR(3000),

DOMAIN VARCHAR(1000),

DOMAIN_URL VARCHAR(1000),

SUB_URL VARCHAR(1000),

SYS_ID INT(3),

TIME_RANGE INT(2)

) partition by range (UNIX_TIMESTAMP(ACCESS_BEGIN_DATE))(

partition PART_ACCESSLOG_20140218 values

(UNIX_TIMESTAMP('2014-02-18 00:00:00')),

partition PART_ACCESSLOG_20140219 values

(UNIX_TIMESTAMP('2014-02-19 00:00:00')),

partition PART_ACCESSLOG_20140220 values

(UNIX_TIMESTAMP('2014-02-20 00:00:00')),

partition PART_ACCESSLOG_20140221 values

(UNIX_TIMESTAMP('2014-02-21 00:00:00')),

partition PART_ACCESSLOG_20140222 values

(UNIX_TIMESTAMP('2014-02-22 00:00:00')),

partition PART_ACCESSLOG_20140223 values

(UNIX_TIMESTAMP('2014-02-23 00:00:00')),

partition PART_ACCESSLOG_20140224 values

(UNIX_TIMESTAMP('2014-02-24 00:00:00')),

partition PART_ACCESSLOG_20140225 values

(UNIX_TIMESTAMP('2014-02-25 00:00:00')),

partition PART_ACCESSLOG_20140226 values

(UNIX_TIMESTAMP('2014-02-26 00:00:00')),

partition PART_ACCESSLOG_20140227 values

(UNIX_TIMESTAMP('2014-02-27 00:00:00')),

partition PART_ACCESSLOG_20140228 values

(UNIX_TIMESTAMP('2014-02-28 00:00:00'))

);

create table TW_PTL_INF_SERVINVOKE

less than

less than

less than

less than

less than

less than

less than

less than

less than

less than

less than

(

INVOKE_ID VARCHAR(50) not null,

INNER_REQUET_ID VARCHAR(20),

SYSTEM_ID VARCHAR(10),

OUTER_REQUET_ID VARCHAR(20),

INVOKE_TYPE INT(2),

CONN_DATE DATETIME,

USED_TIME INT(6),

INVOKE_FLAG CHAR(2),

SERV_NAME VARCHAR(100),

SERV_INPARA TEXT,

SERV_OUTPARA TEXT,

RETCODE VARCHAR(50),

RETMSG VARCHAR(4000),

REMARK VARCHAR(1000),

CLIENT_INPARA TEXT,

CLIENT_OUTPARA TEXT

)partition by range COLUMNS(CONN_DATE)(

partition PART_INF_INVOKELOG_20140218 values less than ('2014-02-18'),

partition PART_INF_INVOKELOG_20140219 values less than ('2014-02-19'),

partition PART_INF_INVOKELOG_20140220 values less than ('2014-02-20'),

partition PART_INF_INVOKELOG_20140221 values less than ('2014-02-21'),

partition PART_INF_INVOKELOG_20140222 values less than ('2014-02-22'),

partition PART_INF_INVOKELOG_20140223 values less than ('2014-02-23'),

partition PART_INF_INVOKELOG_20140224 values less than ('2014-02-24'),

partition PART_INF_INVOKELOG_20140225 values less than ('2014-02-25'),

partition PART_INF_INVOKELOG_20140226 values less than ('2014-02-26'),

partition PART_INF_INVOKELOG_20140227 values less than ('2014-02-27'),

partition PART_INF_INVOKELOG_20140228 values less than ('2014-02-28')

);

修改主键：

alter table Statistic add Id int(11) primary key auto_increment;

mysql:表注释和字段注释

1 创建表的时候写注释

create table test1

(

field_name int comment '字段的注释'

)comment='表的注释';

2 修改表的注释

alter table test1 comment '修改后的表的注释';

3 修改字段的注释

alter table test1 modify column field_name int comment '修改后的字段注释';

--注意：字段名和字段类型照写就行

4 查看表注释的方法

--在生成的SQL语句中看

show create table test1;

--在元数据的表里面看

use information_schema;

select * from TABLES where TABLE_SCHEMA='my_db' and TABLE_NAME='test1'

5 查看字段注释的方法

--show

show full columns from test1;

--在元数据的表里面看

select * from COLUMNS where TABLE_SCHEMA='my_db' and TABLE_NAME='test1'

建表时控制主键的起点【auto_increment=1001】：

create table emb_t_dictBusType

(

emb_c_busTypeID int not null auto_increment,

emb_c_busTypeEnName varchar(255) not null,

emb_c_busTypeZhName varchar(255) not null,

primary key(emb_c_busTypeID)

)engine=INNODB auto_increment=1001 default charset=gbk;

不控制主键的起点

create table emb_t_dictBusType

(

emb_c_busTypeID int not null auto_increment,

emb_c_busTypeEnName varchar(255) not null,

emb_c_busTypeZhName varchar(255) not null,

primary key(emb_c_busTypeID)

)engine=INNODB default charset=gbk;

显示表的结构：

DESCRIBE tablename;

创建临时表：(建立临时表zengchao)

create temporary table zengchao(name varchar(10));

从已经有的表中复制表的结构

create table table2 select * from table1 where 1<>1;

复制表

create table table2 select * from table1;

对表重新命名

alter table table1 rename as table2;

修改列的类型

alter table table1 modify id int unsigned;//修改列id的类型为int unsigned

alter table table1 change id sid int unsigned;//修改列id的名字为sid，而且把属性修改为int unsigned

创建索引

alter table table1 add index ind_id (id);

create index ind_id on table1 (id);

create unique index ind_id on table1 (id);//建立唯一性索引

删除索引

drop index idx_id on table1;

alter table table1 drop index ind_id;

联合字符或者多个列(将列id与":"和列name和"="连接)

select concat(id,':',name,'=') from students;

limit(选出10到20条)<第一个记录集的编号是0>

select * from students order by id limit 9,10;

分析索引效率

方法：在一般的SQL语句前加上explain；

分析结果的含义：

1）table：表名；

2）type：连接的类型，(ALL/Range/Ref)。其中ref是最理想的；

3）possible_keys：查询可以利用的索引名；

4）key：实际使用的索引；

5）key_len：索引中被使用部分的长度（字节）；

6）ref：显示列名字或者"const"（不明白什么意思）；

7）rows：显示MySQL认为在找到正确结果之前必须扫描的行数；

8）extra：MySQL的建议；

Mysql驱动和spring配置、ties修改

配置修改

t=ialect

ClassName=

=jdbc:mysql://172.19.12.15:3306/b2b2c

可以设定链接的字符集，防止乱码：

jdbc:mysql://localhost:3306/ssh?useUnicode=true&characterEncoding=UTF-8

mysql maven坐标

mysql

mysql-connector-java

5.1.6

各种数据库及其数据源配置参数

数据库 Oracle

驱动程序 Driver

URL jdbc:oracle:thin:@localhost:1521:orcl

数据库 DB2

驱动程序 2Driver

URL jdbc:db2://localhost:5000/testDB

数据库 SQL Server

驱动程序 verDriver

URL oft:sqlserver://localhost:1433;DatabaseName=testDB

数据库 Sybase

驱动程序 ver

URL jdbc:sybase:Tds:localhost:5007/testDB

数据库 Informix

驱动程序 ver

URL jdbc:informixsqli://123.45.67.89:1533/myDB:INFORMIXSERVER=myserver

数据库 Mysql

驱动程序

URL jdbc:mysql://localhost:3306/testDB

数据库 PostgreSQL

驱动程序

URL jdbc:postgresql://localhost/testDB

数据库 HSQLDB

驱动程序 iver

URL jdbc:hsqldb:hsql://llocalhost:9902

函数和存储过程无法建立：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL

DATA in its declaration and binary logging is enabled (you *might* want to use the less safe

log_bin_trust_function_creators variable)

解决方法：

set global log_bin_trust_function_creators=TRUE;

MySQL DATE_ADD() 函数

DATE_ADD() 函数向日期添加指定的时间间隔。

语法

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

Type 值

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

实例

假设我们有如下的表：

OrderId

1

ProductName

'Computer'

OrderDate

2008-12-29 16:25:46.635

现在，我们希望向 "OrderDate" 添加 2 天，这样就可以找到付款日期。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS

OrderPayDate

FROM Orders

结果：

OrderId

OrderPayDate

1

2008-12-31 16:25:46.635

Mysql5.1 建表错误记录

1.分区字段必须包含在主键中

2 mysql单表多timestamp报错#1293 - Incorrect table definition; there can be only one

TIMESTAMP column with C解决【tw_ptl_accesslog】

简述：一个表中不能包含多个timestamp字段，特别是设置为now。

一个表中出现多个timestamp并设置其中一个为current_timestamp的时候经常会遇到

#1293 - Incorrect table definition; there can be only oneTIMESTAMP column with

CURRENT_TIMESTAMP in DEFAULT or ON UPDATEclause

原因是当你给一个timestamp设置为on updatecurrent_timestamp的时候，其他的timestamp字段需要显式设定default值

但是如果你有两个timestamp字段，但是只把第一个设定为current_timestamp而第二个没有设定默认值，mysql也能成功建表,但是反过来就不行...

N.B:上面的解释是网上的，实际上on updatecurrent_timestamp（在navicat中文版中为“刷新当前时间戳计时”选项）只能设置一个，或者不设置都可以，不能同时设置2个及以上。

Mysql中默认为系统时间使用 timestamp default now();

如果分区字段使用timestamp类型，使用partition by range

(UNIX_TIMESTAMP(LOGIN_DATE))

此时间类型为datetime，使用partition by range (to_days (LOGIN_DATE))

在accesslog表建立过程中就存在此冲突。

注意：

1. primary key和unique key必须包含在分区key的一部分，否则在创建primary key和unique index时会报”ERROR 1503 (HY000)“

mysql> create unique index idx_employees1_job_code on employees1(job_code);

ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's

partitioning function

或

mysql> ALTER TABLE `skate`.`employees1` ADD PRIMARY KEY (`id`) ;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's

partitioning function

2. 范围分区添加分区只能在最大值后面追加分区

3. 所有分区的engine必须一样

4. 范围分区分区字段：integer、数值表达式、日期列，日期函数表达式(如year(),to_days(),to_seconds(),unix_timestamp())

Mysql区分大小写

1、linux下mysql安装完后是默认：区分表名的大小写，不区分列名的大小写；

2、用root帐号登录后，在/etc/ 中的[mysqld]后添加添加lower_case_table_names=1，重启MYSQL服务，这时已设置成功：不区分表名的大小写；

lower_case_table_names参数详解：

lower_case_table_names = 0

其中 0：区分大小写，1：不区分大小写

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

1、数据库名与表名是严格区分大小写的；

2、表的别名是严格区分大小写的；

3、列名与列的别名在所有的情况下均是忽略大小写的；

4、变量名也是严格区分大小写的；

MySQL在Windows下都不区分大小写。

3、如果想在查询时区分字段值的大小写，则：字段值需要设置BINARY属性，设置的方法有多种：

A、创建时设置：

CREATE TABLE T(

A VARCHAR(10) BINARY

);

B、使用alter修改：

ALTER TABLE `tablename` MODIFY COLUMN `cloname` VARCHAR(45) BINARY;

C、mysql table editor中直接勾选BINARY项。

乱码解决，更详细的看下字符集设置这篇

在[mysqld] 下增加下句

default-character-set=utf8

This function has none of DETERMINISTIC, NO SQL解决办法

This function has none of DETERMINISTIC, NO SQL解决办法

创建存储过程时

出错信息：

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its

declaration and binary logging is enabled (you *might* want to use the less safe

log_bin_trust_function_creators variable)

原因：

这是我们开启了bin-log, 我们就必须指定我们的函数是否是

1 DETERMINISTIC 不确定的

2 NO SQL 没有SQl语句，当然也不会修改数据

3 READS SQL DATA 只是读取数据，当然也不会修改数据

4 MODIFIES SQL DATA 要修改数据

5 CONTAINS SQL 包含了SQL语句

其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log,

我们就必须为我们的function指定一个参数。

解决方法：

SQL code

mysql> show variables like 'log_bin_trust_function_creators';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | OFF |

+---------------------------------+-------+

mysql> set global log_bin_trust_function_creators=1;

mysql> show variables like 'log_bin_trust_function_creators';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin_trust_function_creators | ON |

+---------------------------------+-------+

这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在配置文件中添加：

log_bin_trust_function_creators=1