Mysql 转 SinoDB实用参考

Chapter 1  概述

将数据库迁移到SinoDB主要包括三个步骤： 数据库架构迁移(Schema/DDL)、数据迁移(Data)和应用迁移(Application)。本文将以数据库架构迁移和应用迁移这两个步骤介绍SinoDB不同于Mysql的技术特点，以及这两个数据库差异的转换方法与技巧。

Chapter 2  数据库对象迁移
2.1 数据类型

数字类型

Mysql	SinoDB
TINYINT	SMALLINT
SMALLINT	SMALLINT
MEDIUMINT	INTEGER
INT	INTEGER
INT AUTO_INCREMENT	SERIAL 自增长整数
BIGINT	INT8
REAL	DOUBLE PRECISION
DOUBLE	DOUBLE PRECISION
FLOAT	DOUBLE PRECISION
TINYINT UNSIGNED	SMALLINT
SMALLINT UNSIGNED	INTEGER / SMALLINT
BIGINT UNSIGNED	INT8
REAL UNSIGNED	DOUBLE PRECISION
DOUBLE UNSIGNED	DECIMAL(p,s)  optional: DOUBLE PRECESION
FLOAT UNSIGNED	DOUBLE PRECISION
DECIMAL UNSIGNED	DECIMAL(p,s)
NUMERIC UNSIGNED	DECIMAL(p,s)

字符类型

SinoDB中的字符类型有CHAR, VARCHAR 和 LVARCHAR，其中CHAR和LVARCAHR的长度都是1-32739，VARCHAR的长度是1-255。

VARCHAR(n)    n范围 0-255	VARCHAR(n)
VARCHAR(n)    n范围 >255	LVARCHAR(n)
CHAR(n)	CHAR(n)
TEXT    范围<32739	LVARCHAR(n)

日期/时间类型

SinoDB的常用时间类型有DATE和DATETIME ，这是两个精度不同的时间类型。DATE只包含日期数据。DATETIME除了日期之外，还可以通过不同的定义来定义更精确的时间，最大精确到百分之一毫秒，常用的精度为DATETIME YEAR TO SECOND。TIMESTAMP类型也需要转换为DATETIME YEAR TO FRACTIONS(5)。

DATE	DATE 时间精度为日
TIME	DATETIME HOUR TO FRACTION
TIMESTAMP	DATETIME YEAR TO FRACTION
DATETIME	DATETIME YEAR TO FRACTION(5)
YEAR	CHAR(4)
INTERVAL	INTERVAL 时间间隔，可精确到秒

大对象类型：

TINYBLOB	BYTE / BLOB
BLOB	BLOB / BYTE
MEDIUMBLOB	BYTE / BLOB
LONGBLOB	BYTE / BLOB
TINYTEXT	TEXT
TEXT	TEXT
MEDIUMTEXT	TEXT
LONGTEXT	TEXT

2.2   表

2.2.1 建普通表

SinoDB中建表语句与Mysql是非常相似的，几乎没有复杂的例外，只有一些细节需要修改，另外SinoDB不支持字段的描述，需要去掉或者注释掉。

Mysql	SinoDB
CREATE TABLE “MYTAB”(    “id” INT(11) NOT NULL AUTO_INCREMENT,    “userid” BIGINT DEFAULT NULL COMMENT '用户编号',    “cnt” INT(10) DEFAULT ‘0’,    “desc” CHAR(10),    “status” VARCHAR(1),    “remark” VARCHAR(300),      PRIMARY KEY(“id”) )ENGINE=EXPRESS DEFAULT CHARSET=utf8 COMMENT='个人客户资料表'；	CREATE TABLE MYTAB(    Id SERIAL NOT NULL ,    userid INT(8) DEFAULT NULL ,    cnt INTEGER DEFAULT 0,    desc CHAR(10),    status VARCHAR(1),    Remark LVARCHAR(300),    PRIMARY KEY(id) )；

l  在整数指定DEFAULT 值时，Mysql的可以加引号‘’，但SinoDB不能加。

l  自增长整数用SERIAL类型替换。

l  VARCHAR(n)，n>255时用类型LVARCHAR(n)替换。

l  如有允许NULL值（DEFAULT NULL），在SinoDB中可移除。

2.2.2   建临时表

Mysql临时表通过CREATE TEMPORARY TABLE语句创建，SinoDB通过CREATE TEMP TABLE语句创建。

Mysql	SinoDB
CREATE TEMPORARY TABLE temp_tab select * from tab；	CREATE TEMP TABLE temp_tab(col1 INT,col2 CHAR(10),...) ; 或者隐式创建临时表 select * from tab into temp temp_tab;

注：创建临时表不能和现有的表同名。

SinoDB删除临时表：drop table temp_tab 或 drop table if exists temp_tab;

2.3  索引

SinoDB建立索引的语法与Mysql 基本一致。

2.4   约束

SinoDB建立约束的语法与Mysql基本一致。

2.5  视图

SinoDB建立视图的语句与Mysql基本一致。

Chapter 3   应用迁移

3.1 SPL(Stored Procedure Language)          

3.1.1  创建存储过程

Mysql	SinoDB
DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN     SELECT COUNT(*) INTO s FROM students; END //	CREATE [DBA] PROCEDURE 过程名（参数[, 参数 [ ,...]] ) RETURNING 子句 语句块 END PROCEDURE;

3.1.2 会话变量

Mysql中使用如@变量名称表示全局变量，即session变量。此处的session变量不需要声明，会自动根据值类型来确定类型，在SinoDB中使用全局变量或定义变量来替换。

3.1.3变量定义

Mysql	SinoDB
DECLARE 变量名1[,变量名2...] 数据类型 [默认值];	用DEFINE 语句定义变量，其类型可以是除SERIAL 数据类型外的所有SQL 数据类型；可以使用LIKE定义与字段类型一致的数据类型； 定义全局变量：define global global_var int default 1;

3.1.4变量赋值

Mysql	SinoDB
SET 变量名 = 变量值； Select c1 from tab into 变量名；	利用LET 语句 利用SELECT ... INTO 变量名from tab 语句 利用CALL 语句 利用EXECUTE PROCEDURE ...INTO 语句

3.1.5 返回值

Mysql	SinoDB
OUT参数	RETURN 语句从存储过程中返回。返回值可以有零个或多个，也可以返回多条记录。

3.1.6 游标

SinoDB中，全局游标可以被临时表替代。临时表的创建相当于游标的打开，临时表可以和游标一样使用，使用后，临时表可以被删除，就相当于关闭游标。

FOREACH 隐式定义并打开一个游标；

-FOREACH [WITH HOLD] SELECT ... INTO 语句               

语句块 END FOREACH；

-FOREACH 游标名[WITH HOLD] FOR SELECT ... INTO 语句

语句块 END FOREACH；

–FOREACH EXECUTE PROCEDURE 存储过程名(参数...参数)

INTO 变量[, 变量[, ...]] 语句块 END FOREACH；

例子（游标）：

foreach cur1 for

select ship_date into p_ship_date from orders

where order_date < today - 100

if p_ship_date is not null then

delete fromorders where current of cur1;

end if;

end foreach;

Mysql

SinoDB

create procedure p2() begin         declare row_cat_id int;         declare row_cat_name varchar(90);         declare row_parent_id int;         declare getcategory cursor for select cat_id,cat_name,parent_id from category;         open getcategory;         fetch getcategory into row_cat_id,row_cat_name,row_parent_id;         select row_cat_id,row_cat_name,row_parent_id;         close getcategory; end

CREATE PROCEDURE cur_proc(id INTEGER) RETURNING INTEGER;         DEFINE psql VARCHAR(250);         DEFINE docid INTEGER;         LET psql = "select docid,xml_data from boats where docid < ?";         PREPARE stmt FROM psql;         DECLARE cust_cur cursor FOR stmt;         OPEN cust_cur USING id;         FETCH cust_cur INTO docid;         IF (SQLCODE != 100) THEN         RETURN docid;         END IF         CLOSE cust_cur;         FREE cust_cur;         FREE stmt; END PROCEDURE;

3.1.7动态SQL

SinoDB在存储过程中支持动态SQL。动态SQL是指可以在运行期间根据用户提供的信息动态地构建和执行的SQL语句。

Mysql	SinoDB
PREPARE stmt FROM xxx;    EXECUTE stmt;    deallocate prepare stmt;	EXECUTE IMMEDIATE xxx; 示例： CREATE PROCEDURE create_tab (table_name CHAR(128), column_list CHAR(512))    DEFINE l_crtstmt   CHAR(1024);    LET l_crtstmt = "CREATE TABLE " || table_name ||"("|| column_list || " )";    EXECUTE IMMEDIATE l_crtstmt; END PROCEDURE; EXECUTE PROCEDURE create_tab ("tmp_cust","cust_num INTEGER,cust_fname CHAR(30)");

3.1.8返回错误信息

Mysql	SinoDB
select -1 code,'xxxx' msg;	捕获错误语法： IF  sp_errsql != 0   THEN   RAISE  EXCEPTION sp_errsql, sp_errisam, sp_errstr; END IF; 例子：RAISE EXCEPTION -746,0,"我的自定义错误信息";

3.1.9返回多行记录

Mysql	SinoDB
正常的 select 语句便可	Return c1,c2,c3... with resume; 注：c1,c2,c3...与定义的returning返回类型和数量一致

3.1.10循环语法

LOOP/END LOOP

Mysql	SinoDB
LOOP_LABLE:LOOP       IF v=3 THEN         SET v=v+1;         ITERATE LOOP_LABLE;       END IF;       INSERT INTO t VALUES(v);       SET v=v+1;       IF v>=5 THEN         LEAVE LOOP_LABLE;       END IF;  END LOOP;	LOOP    IF credit_rating IS NULL THEN       CONTINUE;    END IF    IF credit_rating < 3 THEN       EXIT;    END IF END LOOP; 本例中，也可以使用”CONTINUE LOOP”和“EXIT LOOP”；

WHILE

Mysql	SinoDB
SET var=0;       WHILE var<6 DO         INSERT INTO t VALUES (var);         SET var=var+1;       END WHILE ;	LET i = 1; WHILE i < 10     INSERT INTO tab_2 VALUES (i);     LET i = i + 1; END WHILE; 可以在传统的循环语法中使用“CONTINUE WHILE”, “EXIT WHILE”

3.1.11存储过程退出

Mysql	SinoDB
利用leave label方式实现退出 LABEL_PROC: BEGIN     IF p_state = '' THEN     SELECT -2501035 AS return_code, 'state error' AS return_msg;     LEAVE LABEL_PROC;     END IF; END	RETURN 语句从存储过程中返回。返回值可以有零个或多个，也可以返回有条记录； RETURN 语句说明的返回值的个数和类型必须与创建存储过程时说明的返回值的个数和类型一致；

3.2函数

3.2.1常用数值类

Mysql	SinoDB
ABS(n)	ABS(n)
CEILING(n)  返回不小于n的最小整数值	CEIL(n)
FLOOR(n) 返回不大于n的最大整数值	FLOOR(n)
MOD(n,m)  取模运算,返回n被m除的余数	MOD()
POW(x,y) / POWER(x,y)  返回值x的y次幂	POWER(n1,n2)
ROUND(n,d)  返回n的四舍五入值,保留d位小数	ROUND(n1,n2)
SIGN(n)  返回参数的符号(为-1、0或1)	SIGN()
SQRT(n)  返回非负数n的平方根	SQRT(n)
TRUNC(n,d) 保留数字n的d位小数并返回	TRUNC(n1,n2)

3.2.2 字符类

Mysql	SinoDB
CONCAT(c1,c2...,cn)    将c1,c2...,cn连接成字符串，c1,c2,..cn均为字符串	CONCAT(c1,c2) 支持两个字符串的相连。如果多个字符串可用 || 符号进行拼接。
Locate(substr,str)	instr(str,substr)
length(str)	char_length(str) 返回字符串中逻辑字符的计数 length(str)
LOWER() 返回小写字符	LOWER() 返回小写字符
lpad(str,len,padstr)    用字符串padstr填补str左端直到字串长度为len并返回	lpad( string1, padded_length, [ pad_string ] )
left(str,len)  返回字符串str的左端len个字符	left(str,len)
ltrim(str)   返回删除了左空格的字符串str	ltrim(str) ltrim(str1,str2)
lower(str)/lcase(str)  返回小写的字符串str	lower(str)
rpad(str,len,padstr)   用字符串padstr填补str右端直到字串长度为len并返回	rpad( string1, padded_length, [ pad_string ] )
rtrim(str)  返回删除了右空格的字符串str	rtrim(str) rtrim(str1,str2)
right(str,len)   返回字符串str的右端len个字符	right(str,len)
replace(str,from_str,to_str)   用字符串to_str替换字符串str中的子串from_str并返回	REPLACE(c1,c2,c3)
REVERSE() 将指定的字符串的字符排列顺序颠倒	REVERSE()
UPPER(str) 返回str的大写	UPPER(str)
TRIM(str) 去除指定字符前后空格或去除指定字符，而且只能去除单个字符。	Trim(str)
substring_index(str,delim,count)    Substr(str,count)	SUBSTRING_INDEX(str,delim,count) 截取到第n个指定字符串。count为正数时返回左端,否则返回右端子串。 SUBSTR(c1,i,j)
space(n)   返回由n个空格字符组成的一个字符串	SPACE() 返回一个有指定长度的空白字符串

3.2.3日期类

Mysql	SinoDB
DATE_ADD(date,interval expr type)/  DATE_SUB(date,interval expr type)/    ADDDATE(date,interval expr type) /   SUBDATE(date,interval expr type)/  interval expr type 对日期时间进行加减法运算	INTERVAL() 日期的加减操作 select first 1 current a, current- interval(7) day to day b, current - interval(2) hour to hour c, current - interval(2) month to month d, current - interval(2) year to year from systables;
LAST_DAY() 函数返回包含日期d的月份的最后一天：select last_day(now())  ;	select first 1 last_day(sysdate) from systables;
CURTIME() / CURRENT_TIME()  以'hh:mm:ss'或hhmmss格式返回当前时间值	EXTEND(date, hour to second) 指定date数据返回以‘hh:mm:ss’格式的时间值。 SELECT first 1 EXTEND (sysdate, hour to second) FROM systables;
SYADATE()/CURRENT_TIMESTAMP()/now() 函数没有参数，返回当前日期和时间	SYSDATE/CURRENT
CURDATE()/CURDAT_DATE()  以'yyyy-mm-dd'或yyyymmdd格式返回当前日期值	TODAY select first 1 today from systables;
EXTRACT(unit from date) 用于从一个date或者interval类型中截取到特定的部分  Unit 可以是 year\month\day等时间类型 Date为日期时间	YEAR()/MONTH()/DAY() EXTENT(DATE,FORMAT) 返回指定时间段 select first 1 extend(current,year to day) YtoD, extend(current,year to year) year , extend(current,month to month) month , extend(current,day to day) day , extend(current,hour to minute) HtoM  from systables; 结果： ytod       year month day htom  2021-05-16 2021 05    16  15:45
DAY(date) 返回当前日期的天	DAY(date) 返回当前日期的天 select first 1 day(sysdate) from systables;
MONTH(date)  返回date中的月份数值	MONTH(date)
YEAR(date)   返回date的年份	YEAR(date)
DATE_FORMAT(date,format)    To_char(date,format)	to_char(日期|数字|列，转换格式) select first 1 to_char(sysdate,'%Y-%m-%d %H:%M:%S') from systables; 结果：2021-05-16 16:03:15
	to_date(字符串|列，转换格式) select first 1 to_date('1978-10-07 10:00','%Y-%m-%d %H:%M') time from systables;

3.2.4其他函数

Mysql	SinoDB
ifnull(expr1, expr2)	nvl(expr1, expr2)
nullif (expr1, expr2)	nullif (expr1, expr2)
coalesce(expr1, expr2, ...)	coalesce(expr1, expr2, ...)
COUNT(DISTINCT|ALL)	COUNT(DISTINCT|ALL)
MAX(DISTINCT|ALL)	MAX(DISTINCT|ALL)
MIN(DISTINCT|ALL)	MIN(DISTINCT|ALL)
SUM(DISTINCT|ALL)	SUM()
AVG(DISTINCT|ALL)	AVG(DISTINCT|ALL)
Limit n,m	Skip n first m
AES_ENCRYPT	encrypt_aes
AES_DECRYPT	DECRYPT_CHAR
Uuid	sys_guid