MySQL切GaussDB T总结2【转】

MySQL

GaussDB

说明

USE database_name

删除

COLLATE=‘utf8_general_ci’

删除

不推荐使用的SQL语法。

ENGINE=InnoDB

删除

COMMENT ‘comment’

删除

不推荐使用的SQL语法。

user, type等关键字作为列名

建议改名为user_name等。

作为GaussDB关键字不能作为数据库的列名和表名。

CREATE ALGORITHM=UNDEFINED VIEW view_name AS …

删除ALGORITHM=UNDEFINED

创建视图中不支持ALGORITHM=UNDEFINED的SQL语法。

DEFINER=user_name@host_name

PostgreSQL中，procedure加入SECURITY DEFINER语句表明创建者即拥有运行权限。

在MySQL中，DEFINER用于定义一个procedure的所有者，也就是说只有DEFINER定义的user_name和host_name有运行这个procedure的权限。

FROM database_name.table_name

删除database_name

不支持database_name出现在SQL语句中。

`blablabla`

删除

表名及其attributes都不需要用后引号引起来。

SQL数据类型转换

MySQL

GaussDB

说明

MEDIUMTEXT/LONGTEXT

TEXT

DOUBLE

DOUBLE PRECESION

INT(11)

INTEGER(11)

INT(11) UNSIGNED

INT UNSIGNED

DATETIME

TIMESTAMP

CREATE/ALTER

TABLE/FUNCTION/PROCEDURE

时

需要分割出去的SQL语句

MySQL

GaussDB

说明

CREATE TABLE table_name (

  …,

  UNIQUE KEY key (value)

)；

CREATE TABLE table (

  …

);

ALTER TABLE table_name ADD CONSTRAINT key UNIQUE (value);

将UNIQUE KEY语句从CREATE TABLE ()中删除，在建造完表之后，再加入对于UNIQUE KEY的定义。

CREATE TABLE table_name (

  …,

  UNIQUE INDEX new_unique_name (old_name)

);

CREATE TABLE table_name (

  …

);

CREATE UNIQUE INDEX new_unique_name ON table_name (column_name);

同上，

将UNIQUE INDEX语句从CREATE TABLE ()中删除，在建造完表之后，再加入对于UNIQUE INDEX的定义。

CREATE TABLE table_name (

  …,

  INDEX/KEY new_name(column_name)

);

CREATE TABLE table_name (

  …

);

CREATE INDEX/KEY new_name ON table_name(column_name);

同上，

将INDEX/KEY语句从CREATE TABLE ()中删除，在建造完表之后，再加入对于INDEX/KEY的定义。

CREATE TABLE table_name (

  …,

  INDEX index_name (columns) UISNG BTREE

);

CREATE TABLE table_name(

  …

);

CREATE INDEX index_name ON table_name USING BTREE (columns);

同上，

将INDEX/KEY语句从CREATE TABLE ()中删除，在建造完表之后，再加入对于INDEX USING BTREE的定义。

CREATE TABLE IF NOT EXISTS table_name (

…

) PARTITION BY RANGE (…)

(PARTITION partition_name VALUES LESS THAN (MAX_VALUE));

删除IF NOT EXISTS。

注意：

CREATE TABLE PARTITION的时候，不能有IF NOT EXISTS语句，否则报错。

MAX_VALUE必须在两边加括号，否则报错。

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name data_type NOT NULL FIRST;

ALTER TABLE table_name

ALTER COLUMN column_name TYPE data_type;

ALTER TABLE table_name

ALTER COLUMN column_name SET NOT NULL;

删除FIRST语句。

修改表中列的名称和数据类型时需要分成两个字句修改。不支持表中列的排序，所以需要删除FIRST（在MySQL中将此列放在第一列）。

ADD COLUMN column_name data_type AFTER another_column_name;

删除AFTER another_column_name

不支持表中列的排序。

ALTER TABLE table_name MODIFY COLUMN column_name data_type;

ALTER TABLE table_name ALTER COLUMN column_name TYPE data_type;

更改MODIFY COLUMN的语法。

DROP INDEX index_name ON table_name;

DROP INDEX index_name;

因为index_name是唯一的，所以不需要在后面缀上table_name。

CREATE TABLE table_name (

  …,

  column_name ENUM(…, …, …)

)

CREATE TABLE table_name (

  …,

  column_name enum_name

);

TYPE ENUM enum_name AS (…, …, …);

可以在CREAT TABLE中先将列定义为一个新的枚举类型，然后再分开一个语句定义这个枚举类型。

CREATE FUNCTION/PROCEDURE的语法

大体语法为

CREATE FUNCTION/PROCEDURE (parameters)
AS

BEGIN

…

END;

时间有关的SQL语句

MySQL

GaussDB

说明

DATETIME

TIMESTAMP

数据类型在MySQL中为DATETIME的变量应全部变成TIMESTAMP。

DATE_ADD(), DATE_SUB()

(CURRENT_DATE + INTERVAL ‘3 years’)

时间的加减可以直接用加减号。

DATE_FORMAT(‘2019-02-27’, ‘%Y%m%d’)

TO_CHAR(‘2019-02-27’::DATE, ‘yyyymmdd’)

要将第一个字符串参数转换成DATE，第二个参数中定义需要的时间的格式。

INTERVAL 1 month

INTERVAL ‘1 month’

INTERVAL的时间段描述必须要拿单引号引起来。

CURDATE()

CURRENT_DATE

CURDATE()函数全部替换为CURRENT_DATE。

一些写法、叫法不同的SQL语句

MySQL

GaussDB

说明

IFNULL()

NVL()

IFNULL()函数全部替换为NVL()。

SUBSTRING()

SUBSTR()

SUBSTRING()函数全部替换为SUBSTR()。

LIMIT 0, 10

LIMIT 10 OFFSET 0

LIKE

ILIKE

MySQL中查询默认不区分大小写，但在GaussDB中LIKE是大小写敏感的，ILIKE是大小写不敏感的，因此可以用ILIKE替换原先的LIKE，以确保查询结果准确。

‘xi\’an’

‘xi’’an’

INSERT VALUES的时候，如遇到值有单引号，需要用一个单引号转义另一个单引号，不再用到斜杠转义。

SELECT * FROM table_name WHERE column_name = “some_column_value_here”

SELECT * FROM table_name WHERE column_name = ‘some_column_value_here’

只支持单引号。

一些原本在MySQL中定义了的但是GaussDB没有了的函数

MySQL

GaussDB

说明

FIND_IN_SET(value, set)

value ~ CONCAT (‘(’, replace(set, ‘,’, ‘|’), ‘)’)

FIND_IN_SET()函数在PostgresSQL中没有，而PostgreSQL中相对应的STRING_TO_ARRAY()函数在GaussDB中由于历史原因不支持，因此需要通过基础函数自定义。注意原本set中的数据是以逗号分隔的。

问题

问题描述

SQL语句

解决方法

‘’和NULL

在INSERT VALUES中，’’和NULL都会被GaussDB认成NULL，因此如果在CREATE TABLE的时候对attribute有限制为NOT NULL，‘’也会违反这个限制的。

INSERT (p1, p2) VALUES (‘’, NULL)

1.      可以将空字符串转换为其他字符，比如’’’’，会被以单引号的形式存入

2.      去掉CREATE TABLE时NOT NULL的限制。

因为’’和NULL同被视为NULL，column_name != ‘’, column_name = ‘’等语句会严重影响GaussDB的查询，即如果有这些语句存在在查询中，不会有任何报错，但是也无法返回任何查询结果。

SELECT * FROM table_name WHERE column_name != ‘’;

SELECT * FROM table_name WHERE column_name = ‘’

SELECT * FROM table_name WHERE column_name IS NOT NULL;

SELECT * FROM table_name WHERE column_name IS NULL;

must appear in the GROUP BY clause or be used in an aggregate function报错

在PostgreSQL/GaussDB中，如果有一个字段出现在GROUP BY或者聚合函数中，SELECT中出现的所有字段都必须出现在GROUP BY或者聚合函数中。

SELECT a, b

FROM table_name

GROUP BY a

1.      如果将b加入GROUP BY中不影响期望的查询结果，则可以将b直接加入。

2.      如果将b加入GROUP BY中对期望的查询结果有影响，可以用DISTINCT ON ()代替GROUP BY。

SELECT DISTINCT ON (a) a, b

FROM table_name

SELECT a, COUNT(b)

FROM table_name

因为b被用到了聚合函数中，所以a必须被用到GROUP BY或者聚合函数中，因此，最简单的解决方法就是

SELECT a, COUNT(b)

FROM table_name

GROUP BY a

SELECT a, b, COUNT(c)

FROM table_name

GROUP BY a

1.      在这个情况下，b必须被加到GROUP BY中或被运用到聚合函数中。同上，如果对查询结果没有影响则最简便的方法就是将其加入到GROUP BY中；如果有影响，可以考虑MAX(), MIN()这些聚合函数是否会改变查询结果，如果b是一个字符串类型的话，将其运用到MAX()或者MIN()这样的聚合函数中对查询结果一般不会有太大的影响。

2.      在查询的表不多的情况下，还可以将聚合函数和GROUP BY字段单独选成表，再进行选择。

SELECT subTable.a, b, subTable.totalC

FROM (

  SELECT a, COUNT(c) AS totalC

  FROM table_name

  GROUP BY a

) subTable JOIN table_name

ON table_name.a = subTable.a

AND table_name.c = subTable.c;

3.      还可以考虑用PARTITION BY代替GROUP BY

SELECT a, b, COUNT(c) OVER (PARTITION BY a)

FROM table_name