SQL 兼容性
更新时间：2017-06-07 13:26:11
DRDS 高度兼容 MySQL 协议和语法，但由于分布式数据库和单机数据库存在较大的架构差异，存在 SQL 使用限制。相关兼容性和 SQL 限制描述如下。
DRDS SQL 限制
SQL 大类限制
- 暂不支持用户自定义数据类型、自定义函数。
- 暂不支持视图、存储过程、触发器、游标。
- 暂不支持 BEGIN…END、LOOP…END LOOP、REPEAT…UNTIL…END REPEAT、WHILE…DO…END WHILE 等复合语句。
- 暂不支类似 IF ，WHILE 等流程控制类语句。
小语法限制
DDL
- CREATE TABLE tbl_name LIKE old_tbl_name 不支持拆分表。
- CREATE TABLE tbl_name SELECT statement 不支持拆分表。
DML
- 暂不支持 SELECT INTO OUTFILE/INTO DUMPFILE/INTO var_name。
- 暂不支持 INSERT DELAYED Syntax。
- 暂不支持非 WHERE 条件的 Correlate Subquery。
- 暂不支持 SQL 中带聚合条件的 Correlate Subquery。
- 暂不支持 SQL 中对于变量的引用和操作，比如 SET @c=1, @d=@c+1; SELECT @c, @d。
数据库管理
- SHOW WARNINGS Syntax 不支持 LIMIT/COUNT 的组合。
- SHOW ERRORS Syntax 不支持 LIMIT/COUNT 的组合。
DRDS SQL 兼容
协议兼容
DRDS 支持 MySQL Workbench , Navicat For MySQL , SQLyog 等主流客户端。
DDL 语法兼容
- CREATE TABLE Syntax
- CREATE INDEX Syntax
- DROP TABLE Syntax
- DROP INDEX Syntax
- ALTER TABLE Syntax
- TRUNCATE TABLE Syntax
DML 语法兼容
- INSERT Syntax
- REPLACE Syntax
- UPDATE Syntax
- DELETE Syntax
- Subquery Syntax
- Scalar Subquery
- Comparisons Subquery
- Subqueries with ANY, IN, or SOME
- Subqueries with ALL
- Row Subqueries
- Subqueries with EXISTS or NOT EXISTS
- Subqueries in the FROM Clause
- SELECT Syntax
Prepare 语法兼容
- PREPARE Syntax
- EXECUTE Syntax
- DEALLOCATE PREPARE Syntax
数据库管理语法兼容
- SET Syntax
- SHOW Syntax
- SHOW COLUMNS Syntax
- SHOW CREATE TABLE Syntax
- SHOW INDEX
- SHOW TABLES Syntax
- SHOW TABLE STATUS Syntax
- SHOW TABLE STATUS Syntax
- SHOW TABLES Syntax
- SHOW VARIABLES Syntax
- SHOW WARNINGS Syntax
- SHOW ERRORS Syntax
- 其他 SHOW 指令会默认下发到 DB 处理，结果数据没有进行分库数据合并
- KILL ‘PROCESS_ID’ ( DRDS 不支持 KILL QUERY 指令，只支持 KILL ‘PROCESS_ID’ )
数据库工具指令
- DESCRIBE Syntax
- EXPLAIN Syntax
- USE Syntax
DRDS 自定义指令
- SHOW SEQUENCES / CREATE SEQUENCE / ALTER SEQUENCE / DROP SEQUENCE 【 DRDS 全局唯一数字序列管理】
- SHOW PARTITIONS FROM TABLE 【查询表的拆分字段】
- SHOW TOPOLOGY FROM TABLE 【查询表的物理拓扑】
- SHOW BROADCASTS 【查询所有广播表】
- SHOW RULE [FROM TABLE] 【查询表拆分定义】
- SHOW DATASOURCES 【查询后端 DB 连接池定义】
- SHOW DBLOCK / RELEASE DBLOCK 【分布式 LOCK 定义】
- SHOW NODE 【查询读写库流量】
- SHOW SLOW 【查询慢 SQL 列表】
- SHOW PHYSICAL_SLOW 【查询物理 DB 执行慢 SQL 列表】
- TRACE SQL_STATEMENT / SHOW TRACE 【跟踪 SQL 整个执行过程】
- EXPLAIN [DETAIL/EXECUTE] SQL_STATEMENT 【分析 DRDS 执行计划和物理 DB 上的执行计划】
- RELOAD USERS 【同步 DRDS 控制台用户信息到 DRDS SERVER】
- RELOAD SCHEMA 【清理 DRDS 对应 DB 库数据缓存,比如 SQL 解析/语法树/表结构缓存】
- RELOAD DATASOURCES 【重建后端与所有 DB 的连接池】
数据库函数
- 带拆分键的 SQL ，所有 MySQL 函数支持。
- 不带拆分键的 SQL，部分函数支持。
操作符函数
| 函数表达式 | 函数描述 |
|---|---|
| AND, && | Logical AND |
| = | Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement) |
| BETWEEN … AND … | Check whether a value is within a range of value |
| BINARY | Cast a string to a binary string |
| & | Bitwise AND |
| ~ | Bitwise inversion |
| ^ | Bitwise XOR |
| DIV | Integer division |
| / | Division operator |
| <=> | NULL-safe equal to perator |
| = | Equal operator |
| >= | Greater than or equal operator |
| > | Greater than operator |
| IS NOT NULL | NOT NULL value test |
| IS NOT | Test a value against a boolean |
| IS NULL | NULL value test |
| IS | Test a value against a boolean |
| << | Left shift |
| <= | Less than or equal operator |
| < | Less than operator |
| LIKE | Simple pattern matching |
| - | Minus operator |
| %, | MODModulo operator |
| NOT BETWEEN … AND … | Check whether a value is not within a range of values |
| !=, <> | Not equal operator |
| NOT LIKE | Negation of simple pattern matching |
| NOT REGEXP | Negation of REGEXP |
| NOT, ! | Negates value |
| OR | Logical OR |
| + | Addition operator |
| REGEXP | Pattern matching using regular expressions |
| >> | Right shift |
| RLIKE | Synonym for REGEXP |
| * | Multiplication operator |
| - | Change the sign of the argument |
| XOR | Logical XOR |
| Coalesce | Return the first non-NULL argument |
| GREATEST | Return the largest argument |
| LEAST | Return the smallest argument |
| STRCMP | Compare two strings |
流程控制函数
| Function | Description |
|---|---|
| CASE | Case operator |
| IF() | If/else construct |
| IFNULL() | Null if/else construct |
| NULLIF() | Return NULL if expr1 = expr2 |
数学函数
| Function | Description |
|---|---|
| ABS() | Return the absolute value |
| ACOS() | Return the arc cosine |
| ASIN() | Return the arc sine |
| ATAN2(),ATAN() | Return the arc tangent of the two arguments |
| ATAN() | Return the arc tangent |
| CEIL() | Return the smallest integer value not less than the argument |
| CEILIG() | Return the smallest integer value not less than the argument |
| CONV() | Convert numbers between different number bases |
| COS() | Return the cosine |
| COT() | Return the cotangent |
| CRC32() | Compute a cyclic redundancy check value |
| DEGREES() | Convert radians to degrees |
| DIV | Intger division |
| EXP() | Raise to the power of |
| FLOOR() | Return the largest integer value not greater than the argument |
| LN() | Return the natural logarithm of the argument |
| LOG10() | Return the base-10 logarithm of the argument |
| LOG2() | Return the base-2 logarithm of the argument |
| LOG() | Return the natural logarithm of the first argument |
| MOD() | Return the remainder |
| %, MOD | Modulo operator |
| PI() | Return the value of pi |
| POW() | Return the argument raised to the specified power |
| POWER() | Return the argument raised to the specified power |
| RADIANS() | Return argument converted to radians |
| RAND() | Return a random floating-point value |
| ROUND() | Round the argument |
| SIGN() | Return the sign of the argument |
| SIN() | Return the sine of the argument |
| SQRT() | Return the square root of the argument |
| TAN() | Return the tangent of the argument |
| TRUNCATE( | Truncate to specified number of decimal places |
字符串函数
| Function | Description |
|---|---|
| ASCII() | Return numeric value of left-most character |
| BIN() | Return a string containing binary representation of a number |
| BIT_LENGTH() | Return length of argument in bits |
| CHAR_LENGTH() | Return number of characters in argument |
| CHAR() | Return the character for each integer passed |
| CHARACTER_LENGTH() | Synonym for CHAR_LENGTH() |
| CONCAT_WS() | Return concatenate with separator |
| CONCAT() | Return concatenated string |
| ELT() | Return string at index number |
| EXPORT_SET() | Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string |
| FIELD() | Return the index (position) of the first argument in the subsequent arguments |
| FIND_IN_SET() | Return the index position of the first argument within the second argument |
| FORMAT() | Return a number formatted to specified number of decimal places |
| HEX() | Return a hexadecimal representation of a decimal or string value |
| INSERT() | Insert a substring at the specified position up to the specified number of characters |
| INSTR() | Return the index of the first occurrence of substring |
| LCASE() | Synonym for LOWER() |
| LEFT() | Return the leftmost number of characters as specified |
| LENGTH() | Return the length of a string in bytes |
| LIKE | Simple pattern matching |
| LOCATE() | Return the position of the first occurrence of substring |
| LOWER() | Return the argument in lowercase |
| LPAD() | Return the string argument, left-padded with the specified string |
| LTRIM() | Remove leading spaces |
| MAKE_SET() | Return a set of comma-separated strings that have the corresponding bit in bits set |
| MID() | Return a substring starting from the specified position |
| NOT LIKE | Negation of simple pattern matching |
| NOT REGEXP | Negation of REGEXP |
| OCT() | Return a string containing octal representation of a number |
| OCTET_LENGTH() | Synonym for LENGTH() |
| ORD() | Return character code for leftmost character of the argument |
| POSITION() | Synonym for LOCATE() |
| QUOTE() | Escape the argument for use in an SQL statement |
| REPEAT() | Repeat a string the specified number of times |
| REPLACE() | Replace occurrences of a specified string |
| REVERSE() | Reverse the characters in a string |
| RIGHT() | Return the specified rightmost number of characters |
| RPAD() | Append string the specified number of times |
| RTRIM() | Remove trailing spaces |
| SPACE() | Return a string of the specified number of spaces |
| STRCMP() | Compare two strings |
| SUBSTR() | Return the substring as specified |
| SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter |
| SUBSTRING() | Return the substring as specified |
| TRIM() | Remove leading and trailing spaces |
| UCASE() | Synonym for UPPER() |
| UNHEX() | Return a string containing hex representation of a number |
| UPPER() | Convert to uppercase |
时间函数
| Function | Description |
|---|---|
| ADDDATE() | Add time values (intervals) to a date value |
| ADDTIME() | Add time |
| CURDATE() | Return the current date |
| CURRENT_DATE() | CURRENT_DATE Synonyms for CURDATE() |
| CURRENT_TIME() | CURRENT_TIME Synonyms for CURTIME() |
| CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP Synonyms for NOW() |
| CURTIME() | Return the current time |
| DATE_ADD() | Add time values (intervals) to a date value |
| DATE_FORMAT() | Format date as specified |
| DATE_SUB() | Subtract a time value (interval) from a date |
| DATE() | Extract the date part of a date or datetime expression |
| DATEDIFF() | Subtract two dates |
| DAY() | Synonym for DAYOFMONTH() |
| DAYNAME() | Return the name of the weekday |
| DAYOFMONTH() | Return the day of the month (0-31) |
| DAYOFWEEK() | Return the weekday index of the argument |
| DAYOFYEAR() | Return the day of the year (1-366) |
| EXTRACT() | Extract part of a date |
| FROM_DAYS() | Convert a day number to a date |
| FROM_UNIXTIME() | Format UNIX timestamp as a date |
| GET_FORMAT() | Return a date format string |
| HOUR() | Extract the hour |
| LAST_DAY() | Return the last day of the month for the argument |
| LOCALTIME() | LOCALTIME Synonym for NOW() |
| LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym for NOW() |
| MAKEDATE() | Create a date from the year and day of year |
| MAKETIME() | Create time from hour, minute, second |
| MICROSECOND() | Return the microseconds from argument |
| MINUTE() | Return the minute from the argument |
| MONTH() | Return the month from the date passed |
| MONTHNAME() | Return the name of the month |
| NOW() | Return the current date and time |
| PERIOD_ADD() | Add a period to a year-month |
| PERIOD_DIFF() | Return the number of months between periods |
| QUARTER() | Return the quarter from a date argument |
| SEC_TO_TIME() | Converts seconds to ‘HH:MM:SS’ format |
| SECOND() | Return the second (0-59) |
| STR_TO_DATE() | Convert a string to a date |
| SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments |
| SUBTIME() | Subtract times |
| SYSDATE() | Return the time at which the function executes |
| TIME_FORMAT() | Format as time |
| TIME_TO_SEC() | Return the argument converted to seconds |
| TIME() | Extract the time portion of the expression passed |
| TIMEDIFF() | Subtract time |
| TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
| TIMESTAMPADD() | Add an interval to a datetime expression |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
| UNIX_TIMESTAMP() | Return a UNIX timestamp |
| UTC_DATE() | Return the current UTC date |
| UTC_TIME() | Return the current UTC time |
| UTC_TIMESTAMP() | Return the current UTC date and time |
| WEEKDAY() | Return the weekday index |
| WEEKOFYEAR() | Return the calendar week of the date (1-53) |
| YEAR() | Return the year |
类型转换函数
| Function | Description |
|---|---|
| BINARY | Cast a string to a binary string |
| CAST() | Cast a value as a certain type |
| CONVERT() | Cast a value as a certain type |