语言结构
分布式实例支持所有 MySQL 使用的文字格式,包括:
String LiteralsNumeric LiteralsDate and Time LiteralsHexadecimal LiteralsBit-Value LiteralsBoolean LiteralsNULL Values
String Literals
String Literals 是一个 bytes 或者 characters 的序列,两端被单引号
'
或者双引号"
包围,TDSQL MySQL版 目前不支持 ANSI_QUOTES SQL MODE,双引号"
包围的始终认为是 String Literals,而不是 identifier。不支持 character set introducer,即
[_charset_name]'string' [COLLATE collation_name]
这种格式。支持的转义字符:
\\0: ASCII NUL (X’00’) 字符\\‘: 单引号\\“: 双引号\\b: 退格符号\\n: 换行符\\r: 回车符\\t: tab 符(制表符)\\z: ASCII 26 (Ctrl + Z)\\\\: 反斜杠 \\\\%: \\%\\_: _
Numeric Literals
数值字面值包括 integer、Decimal 类型、浮点数字面值。
integer 可以包括
.
作为小数点分隔,数字前可以有-
或者+
来表示正数或者负数。
精确数值字面值可以表示为如下格式:1, .2, 3.4, -5, -6.78, +9.10
。
科学记数法,如下格式:1.2E3, 1.2E-3, -1.2E3, -1.2E-3
。Date and Time Literals
DATE 支持如下格式:
'YYYY-MM-DD' or 'YY-MM-DD''YYYYMMDD' or 'YYMMDD'YYYYMMDD or YYMMDD如:'2012-12-31', '2012/12/31', '2012^12^31', '2012@12@31' '20070523' , '070523'
DATETIME,TIMESTAMP 支持如下格式:
'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS''YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS'YYYYMMDDHHMMSS or YYMMDDHHMMSS如'2012-12-31 11:30:45', '2012^12^31 11+30+45', '2012/12/31 11*30*45', '2012@12@31 11^30^45',19830905132800
Hexadecimal Literals
支持格式如下:
X'01AF'X'01af'x'01AF'x'01af'0x01AF0x01af
Bit-Value Literals
支持格式如下:
b'01'B'01'0b01
Boolean Literals
常量 TRUE 和 FALSE 等于1和0,大小写不敏感。
mysql> SELECT TRUE, true, FALSE, false;+------+------+-------+-------+| TRUE | TRUE | FALSE | FALSE |+------+------+-------+-------+| 1 | 1 | 0 | 0 |+------+------+-------+-------+1 row in set (0.03 sec)
NULL Values
NULL 代表数据为空,大小写不敏感,与 \\N(大小写敏感)同义。
需要注意的是 NULL 跟0并不一样,跟空字符串
''
也不一样。字符集和时区
支持 MySQL 的所有字符集和字符序:
mysql> show character set;+----------+---------------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+---------------------------------+---------------------+--------+| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || ascii | US ASCII | ascii_general_ci | 1 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || greek | ISO 8859-7 Greek | greek_general_ci | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || cp866 | DOS Russian | cp866_general_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European | macroman_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 || utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 || cp1251 | Windows Cyrillic | cp1251_general_ci | 1 || utf16 | UTF-16 Unicode | utf16_general_ci | 4 || utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || utf32 | UTF-32 Unicode | utf32_general_ci | 4 || binary | Binary pseudo charset | binary | 1 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 || gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |+----------+---------------------------------+---------------------+--------+41 rows in set (0.02 sec)
查看当前连接的字符集:
mysql> show variables like "%char%";+--------------------------+-----------------------------------------------------+| Variable_name | Value |+--------------------------+-----------------------------------------------------+| character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /data/tdsql_run/8812/percona-5.7.17/share/charsets/ |+--------------------------+-----------------------------------------------------+
设置当前连接相关的字符集:
mysql> set names utf8;Query OK, 0 rows affected (0.03 sec)mysql> show variables like "%char%";+--------------------------+-----------------------------------------------------+| Variable_name | Value |+--------------------------+-----------------------------------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /data/tdsql_run/8811/percona-5.7.17/share/charsets/ |+--------------------------+-----------------------------------------------------+
说明:
分布式实例不支持设置全局参数,需要调用前台接口设置。
支持通过设置 time_zone 变量修改时区相关的属性:
mysql> show variables like '%time_zone%';+------------------+--------+| Variable_name | Value |+------------------+--------+| system_time_zone | CST || time_zone | SYSTEM |+------------------+--------+2 rows in set (0.00 sec)mysql> create database test;Query OK, 0 rows affected (0.37 sec)mysql> create table test.tt (ts timestamp, dt datetime,c int, PRIMARY KEY(c)) shardkey=c;Query OK, 0 rows affected (0.49 sec)mysql> insert into test.tt (ts,dt,c)values ('2017-10-01 12:12:12', '2017-10-01 12:12:12',1);Query OK, 1 row affected (0.09 sec)mysql> select * from test.tt;+---------------------+---------------------+------+| ts | dt | c |+---------------------+---------------------+------+| 2017-10-01 12:12:12 | 2017-10-01 12:12:12 | 1 |+---------------------+---------------------+------+1 row in set (0.04 sec)mysql> set time_zone = '+12:00';Query OK, 0 rows affected (0.00 sec)mysql> show variables like '%time_zone%';+------------------+--------+| Variable_name | Value |+------------------+--------+| system_time_zone | CST || time_zone | +12:00 |+------------------+--------+2 rows in set (0.00 sec)mysql> select * from test.tt;+---------------------+---------------------+------+| ts | dt | c |+---------------------+---------------------+------+| 2017-10-01 16:12:12 | 2017-10-01 12:12:12 | 1 |+---------------------+---------------------+------+1 row in set (0.06 sec)
数据类型
支持 MySQL 的所有数据类型,包括数字,字符,日期,空间类型,JSON。
数字
整型支持 INTEGER, INT,SMALLINT,TINYINT,MEDIUMINT,BIGINT。
类型 | 字节数 | 最小值(有符号/无符号) | 最大值(有符号/无符号) |
TINYINT | 1 | -128/0 | 127/255 |
SMALLINT | 2 | -32768/0 | 32767/65535 |
MEDIUMINT | 3 | -8388608/0 | 8388607/16777215 |
INT | 4 | -2147483648/0 | 2147483647/4294967295 |
BIGINT | 8 | -9223372036854775808/0 | 9223372036854775807/18446744073709551615 |
浮点类型支持 FLOAT 和 DOUBLE,格式 FLOAT(M,D) 、REAL(M,D) 或 DOUBLE PRECISION(M,D)
定点类型支持 DECIMAL和NUMERIC,格式 DECIMAL(M,D)
字符
支持如下字符类型:
CHAR 和 VARCHAR TypesBINARY 和 VARBINARY TypesBLOB 和 TEXT TypesTINYBLOB,TINYTEXT,MEDIUMBLOB,MEDIUMTEXT,LONGBLOB,LONGTEXTENUM TypeSET Type
日期
支持如下时间类型:
DATE, DATETIME, TIMESTAMP TypesTIME TypeYEAR Type
空间
支持如下空间类型:
GEOMETRYPOINTLINESTRINGPOLYGONMULTIPOINTMULTILINESTRINGMULTIPOLYGONGEOMETRYCOLLECTION
JSON
支持存储 JSON 格式的数据,使得对 JSON 处理更加有效,同时又能提早检查错误:
注意:
对 JSON 类型的字段进行排序时,不支持混合类型排序。如不能将 string 类型和 int 类型做比较,同类型排序只支持数值类型,string 类型,其它类型排序不处理。对下表来说,不支持
select * from t1 order by t1->"$.key2"
,因为排序列中包含了数值和字符串类型。mysql> CREATE TABLE t1 (jdoc JSON,a int,PRIMARY KEY(a)) shardkey=a;Query OK, 0 rows affected (0.30 sec)mysql> INSERT INTO t1 (jdoc,a)VALUES('{"key1": "value1", "key2": "value2"}',1);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO t1 (jdoc,a)VALUES('{"key1": "value1", "key2": 2}',2);mysql> INSERT INTO t1 (jdoc,a)VALUES('[1, 2,',5);ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.mysql> select * from t1;+--------------------------------------+---+| jdoc | a |+--------------------------------------+---+| {"key1": "value1", "key2": "value2"} | 1 || {"key1": "value1", "key2": 2} | 2 |+--------------------------------------+---+2 rows in set (0.00 sec)
函数支持
Control Flow Functions
名称 | 描述 |
CASE | Case operator |
IF() | If/else construct |
IFNULL() | Null if/else construct |
NULLIF() | Return NULL if expr1 = expr2 |
String Functions
名称 | 描述 |
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() | Return the character for each integer passed |
CHAR_LENGTH() | Return number of characters in argument |
CHARACTER_LENGTH() | Synonym for CHAR_LENGTH() |
CONCAT() | Return concatenated string |
CONCAT_WS() | Return concatenate with separator |
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 |
FROM_BASE64() | Decode to a base-64 string and return result |
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 |
LOAD_FILE() | Load the named file |
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 |
MATCH | Perform full-text search |
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 |
REGEXP | Pattern matching using regular expressions |
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 |
RLIKE | Synonym for REGEXP |
RPAD() | Append string the specified number of times |
RTRIM() | Remove trailing spaces |
SOUNDEX() | Return a soundex string |
SOUNDS LIKE | Compare sounds |
SPACE() | Return a string of the specified number of spaces |
STRCMP() | Compare two strings |
SUBSTR() | Return the substring as specified |
SUBSTRING() | Return the substring as specified |
SUBSTRING_INDEX() | Return a substring from a string before the specified number of occurrences of the delimiter |
TO_BASE64() | Return the argument converted to a base-64 string |
TRIM() | Remove leading and trailing spaces |
UCASE() | Synonym for UPPER() |
UNHEX() | Return a string containing hex representation of a number |
UPPER() | Convert to uppercase |
WEIGHT_STRING() | Return the weight string for a string |
Numeric Functions and Operators
名称 | 描述 |
ABS() | Return the absolute value |
ACOS() | Return the arc cosine |
ASIN() | Return the arc sine |
ATAN() | Return the arc tangent |
ATAN2(), ATAN() | Return the arc tangent of the two arguments |
CEIL() | Return the smallest integer value not less than the argument |
CEILING() | 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 | Integer division |
/ | Division operator |
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 |
LOG() | Return the natural logarithm of the first argument |
LOG10() | Return the base-10 logarithm of the argument |
LOG2() | Return the base-2 logarithm of the argument |
- | Minus operator |
MOD() | Return the remainder |
%, MOD | Modulo operator |
PI() | Return the value of pi |
+ | Addition operator |
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 |
* | Multiplication operator |
TRUNCATE() | Truncate to specified number of decimal places |
- | Change the sign of the argument |
Date and Time Functions
名称 | 描述 |
ADDDATE() | Add time values (intervals) to a date value |
ADDTIME() | Add time |
CONVERT_TZ() | Convert from one time zone to another |
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() | Extract the date part of a date or datetime expression |
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 |
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() | Extract the time portion of the expression passed |
TIME_FORMAT() | Format as time |
TIME_TO_SEC() | Return the argument converted to seconds |
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 |
TO_DAYS() | Return the date argument converted to days |
TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
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 |
WEEK() | Return the week number |
WEEKDAY() | Return the weekday index |
WEEKOFYEAR() | Return the calendar week of the date (1-53) |
YEAR() | Return the year |
YEARWEEK() | Return the year and week |
Aggregate (GROUP BY) Functions
名称 | 描述 |
AVG() | Return the average value of the argument |
COUNT() | Return a count of the number of rows returned |
MAX() | Return the maximum value |
MIN() | Return the minimum value |
SUM() | Return the sum |
Bit Functions and Operators
名称 | 描述 |
BIT_COUNT() | Return the number of bits that are set |
& | Bitwise AND |
~ | Bitwise inversion |
| | Bitwise OR |
^ | Bitwise XOR |
<< | Left shift |
>> | Right shift |
Cast Functions and Operators
名称 | 描述 |
BINARY | Cast a string to a binary string |
CAST() | Cast a value as a certain type |
CONVERT() | Cast a value as a certain type |