Mysql的预定义字符

MySQL的预定义字符一直没有认真去研究过，结果前几天写数据库的时候出问题了……ORZ……如果要用这些字符，必须如下方法

 

CREATE TABLE `interval` (begin INT, end INT);

CREATE TABLE mydb.interval (begin INT, end INT);

创建表格的时候系统没有报错，但是做SQL语句查询的时候问题来了，一查才知道，是因为用了div做column的名字，而自己不知道这是MySQL的预定义字符。决定花点时间，好好学习一下，不能再犯类似的错误了。

 

ADD : 修改表结构时用于添加column

ALL: 用在查询的判断中https://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html                  

ALTER: 修改表结构时使用

ANALYZE: 应该是用在数据库管理中analyze table -- analyzes and stores the key distribution for a table。http://dev.mysql.com/doc/refman/5.6/en/analyze-table.html

AND: 判断时常用

AS: 选择时常用

ASC: 排序使用，顺序排列。不过mysql默认的排序就是ASC，可以不写。                     

ASENSITIVE: Google翻译，这个词的意思是敏感性未定型。从MySQL的官方文档看，这是Mysql 5的预定义字符，但是这个字符做什么用，我没有找到相关文档╮(╯_╰)╭。

BEFORE: 用于出发预定义的程序，与trigger一起用，是在某操作事件发生前，触发
BETWEEN：用在条件判断中，用来替换 <= and >=，可以这样写 where t1 between '2012-01-01' and '2012-06-01'

BIGINT: 8 byte integer              

BINARY: is an operator casts the string following it to a binary string
BLOB: a binary large object that can hold a variable amount of data. 与TEXT相对应的。用比特流来存储数据                   

BOTH:  mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');

        -> 'bar'

           http://dev.mysql.com/doc/refman/5.0/en/string-functions.html                     

BY： order by columnX

CALL：invokes a stored procedure that was defined previously with CREATE PROCEDURE. http://dev.mysql.com/doc/refman/5.5/en/call.html

CASCADE：a reference option, Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html                  

CASE：用法很像其他编程语言里的switch(){ case:....}  http://dev.mysql.com/doc/refman/5.0/en/case-statement.html 
CHANGE: 有个命令叫 change master to [option,...] http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html       

CHAR：相对于varchar来说，存储占用空间不变长度，需要一提的是，char（4）占用4bytes，而varchar（4）在4个字符存满的情况下，占用5bytes。http://dev.mysql.com/doc/refman/5.0/en/char.html                      

CHARACTER：用于对CHARACTER SET操作
CHECK：  有check table 命令，检查table的状态和错误http://dev.mysql.com/doc/refman/5.0/en/check-table.html                 

COLLATE：覆盖原字码的字符编码，可以用在比对中如果存在特殊字符的情况下http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html                 

COLUMN：常用，不解释
CONDITION： 没找到，在文档中提到condition的时候，指的是判断的条件语句 when [condtion]，不过似乎本身并没有这个命令。            

CONNECTION：  KILL CONNECTION = KILL ，类似的还有KILL QUERY，用于切断链接或者正在查询中的语句http://dev.mysql.com/doc/refman/5.1/en/kill.html             

CONSTRAINT： The CONSTRAINT clause allows you to name the constraint explicitly, either to make metadata more readable or else to use the name when you want to drop the constraint. The SQL standard requires that the CONSTRAINT clause is optional. If you leave it out, the RDBMS creates a name automatically, and the name is up to the implementation. http://stackoverflow.com/questions/310561/mysql-terminology-constraints-vs-foreign-keys-difference 
CONTINUE：有点像其它语言里循环的时候用的continue http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html                

CONVERT：字符编码转换 http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html              

CREATE：常用，不解释
CROSS：   在MYSQL中 JOIN = CROSS JOIN = INNER JOIN    http://miyulano.iteye.com/admin/blogs/1164659         

CURRENT_DATE：  等同于   CURDATE()             

CURRENT_TIME：等同于  CURTIME() 
CURRENT_TIMESTAMP：等同于 NOW()       

CURRENT_USER: return currentuser@hostname              

CURSOR:  http://dev.mysql.com/doc/refman/5.0/en/cursors.html 
DATABASE: 字面意思，不解释

DATABASES: 最常用的大概是show databases

DAY_HOUR, DAY_MICROSECOND, DAY_MINUTE,  DAY_SECOND:  http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html             

DEC: 十进制 DEC = DECIMAL = FIXED

DECIMAL:  十进制 DEC = DECIMAL = FIXED            

DECLARE:  The DECLARE statement is used to define various items local to a program http://dev.mysql.com/doc/refman/5.0/en/declare.html 
DEFAULT:  常用，不解释                

DELAYED: INSERT DELAYED, 感觉是先写在缓存中，积累到一定量一次写入disk中？http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html

DELETE: 常用，不解释
DESC: 倒序排序

DESCRIBE: DESCRIBE {tablename} = DESC {tablename}

DETERMINISTIC:  http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html 
DISTINCT: 用在select中，有order by的作用http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html

DISTINCTROW:  The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT. http://dev.mysql.com/doc/refman/5.5/en/select.html

DIV: Integer division (整数除法）http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html

DOUBLE: 常用，不解释

DROP: 常用，不解释

DUAL: You are permitted to specify DUAL as a dummy table name in situations where no tables are referenced

EACH：没找到╮(╯_╰)╭

ELSE，ELSEIF：同其他语言

ENCLOSED：FIELDS的选项for LOAD DATA，  controls how to read or write special characters

ESCAPED：同上http://dev.mysql.com/doc/refman/5.1/en/load-data.html

EXISTS：If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE. http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

EXIT：Command，Exit mysql. Same as quit.

EXPLAIN：The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE: http://dev.mysql.com/doc/refman/5.0/en/explain.html

FALSE：常用，不解释
FETCH: 类似mysql_fetch_array这样的，http://dev.mysql.com/doc/refman/5.0/en/fetch.html，http://dev.mysql.com/doc/refman/5.0/en/cursors.html

FLOAT：数据类型，长度4字节 详细：http://www.5iphp.com/node/140                   

FLOAT4, FLOAT8: MySQL allows FLOAT4 and FLOAT8 as synonyms, but what they are synonyms for depends on your version of MySQL:

• Prior to MySQL 3.23.6, FLOAT4 and FLOAT8 are equivalent to FLOAT(10,2) and DOUBLE(16,4).

• For MySQL 3.23.6 and later, FLOAT4 and FLOAT8 are equivalent to FLOAT and DOUBLE.

                    http://etutorials.org/SQL/MySQL/Part+I+General+MySQL+Use/Chapter+2.+Working+with+Data+in+MySQL/MySQL+Column+Types/

FOR: (没找到相关）                       

FORCE：{USE|FORCE} INDEX is applied if present. (If not, the optimizer-determined set of indexes is used.) http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
FOREIGN： foreign key                 

FROM: 常用，不解释                      

FULLTEXT：an index type, the more words in the text, the large size of the index, more: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
GOTO: 编程语言里常见

GRANT： grant user privileges                    

GROUP: 常用
HAVING：用来约束group by http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

HIGH_PRIORITY：HIGH_PRIORITY gives the SELECT higher priority than a statement that updates a table. You should use this only for queries that are very fast and must be done at once. HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION.http://dev.mysql.com/doc/refman/5.5/en/select.html

HOUR_MICROSECOND: format 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_MINUTE: format 'HOURS:MINUTES' 

HOUR_SECOND: format 'HOURS:MINUTES:SECONDS'               

IF: 判断
IGNORE： IGNORE INDEX

IN： Where a in ( x, x, x)                        

INDEX: 索引http://database.51cto.com/art/201010/229475.htm
INFILE:  load data infile http://dev.mysql.com/doc/refman/5.1/en/load-data.html                

INNER: inner join                     

INOUT: 执行前需要被初始化的全局变量http://dev.mysql.com/doc/refman/5.0/en/call.html
INSENSITIVE：与string的匹配相关             

INSERT: 常用，不解释                    

INT：4字节
INT1 :  = TINYINT 1字节  （-128 ~ 127 | 0 - 255）                

INT2 =  Small 2字节（-32768 ~ 32767 | 0 ~ 65535）                    

INT3 = MEDIUMINT 3字节 （1600万+）
INT4 = INT 4字节   （40亿+）               

INT8 = BIGINT 8字节                    

INTEGER = INT
INTERVAL: 用在时间相关的函数里。The INTERVAL keyword and the unit specifier are not case sensitive. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html              

INTO： insert into table                       

IS： is null, is not null. http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
ITERATE:   means “start the loop again.” http://dev.mysql.com/doc/refman/5.0/en/iterate-statement.html              

JOIN: 常用，不解释

KEY:  KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition.  http://stackoverflow.com/questions/924265/what-does-the-key-keyword-mean 
KEYS: (╮(╯_╰)╭)                    

KILL: Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST statement and kill a thread with the KILL thread_id statement. http://dev.mysql.com/doc/refman/5.0/en/kill.html                      

LABEL: 用来标记loop  http://dev.mysql.com/doc/refman/5.1/en/statement-labels.html 
LEADING：mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');

        -> 'barxxx'

        http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

LEAVE:  LEAVE label

            This statement is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block, LEAVE exits the program.

            http://dev.mysql.com/doc/refman/5.0/en/leave-statement.html                    

LEFT: 常用 left join
LIKE：常用   like '%%s%'                 

LIMIT: 常用                     

LINEAR： linear hashing  http://dev.mysql.com/doc/refman/5.1/en/partitioning-linear-hash.html 
LINES: load data infile 中，用于定义line的起始 http://dev.mysql.com/doc/refman/5.1/en/load-data.html                  

LOAD：load data infile http://dev.mysql.com/doc/refman/5.1/en/load-data.html                     

LOCALTIME：等同于 NOW() 
LOCALTIMESTAMP：等同于NOW()          

LOCK：  lock table http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html                   

LONG：在mysql中相当于 MEDIUMTEXT 长度是 L + 3 bytes, where L < 2²⁴ 
LONGBLOB：二进制方式存储的LONG，用于存放可变的数据，长度 L + 4 bytes, where L < 2³²                

LONGTEXT：  L + 4 bytes, where L < 2³²                

LOOP： 循环
LOW_PRIORITY：To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY attribute.http://dev.mysql.com/doc/refman/5.0/en/table-locking.html            

MATCH:  MATCH (col1,col2,...) AGAINST (expr [search_modifier])  感觉像精确搜索，查找COL1，COL2中是否有匹配的的字段，但是，当匹配字段的出现率在表中高于50%时，返回结果为0 http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html                     

MEDIUMBLOB:  A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB,MEDIUMBLOB . 长度等同于MEDIUMTEXT，都是L + 3 bytes, where L < 2²⁴http://dev.mysql.com/doc/refman/5.0/en/blob.html 

MEDIUMINT：3byte长的int，（+-）800 0000+               

MEDIUMTEXT：L + 3 bytes, where L < 2²⁴               

MIDDLEINT: 在网络上找到的说法是mysql 没有 MIDDLEINT 这个数据类型，但是它的确在预定义词组里╮(╯_╰)╭
MINUTE_MICROSECOND：  fomate 'MINUTES:SECONDS.MICROSECONDS'http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html 

MINUTE_SECOND:     fomate    'MINUTES:SECONDS' http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html     

MOD:  求余数，等同于%（Modulo operation. Returns the remainder of N divided by M ）
MODIFIES：   MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT orDELETE) http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html             

NATURAL ： The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.                  

NOT：常用
NO_WRITE_TO_BINLOG: By default, the server writes FLUSH statements to the binary log so that they replicate to replication slaves. To suppress logging, use the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.      

NULL：常用                     

NUMERIC: ╮(╯_╰)╭numeric functions  http://dev.mysql.com/doc/refman/5.0/en/numeric-functions.html 
ON：和join一起用

OPTIMIZE：For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html                 

OPTION: ╮(╯_╰)╭
OPTIONALLY:  If you specify OPTIONALLY, the ENCLOSED BY character is used only to enclose values from columns that have a string data type (such as CHAR, BINARY, TEXT, or ENUM): http://dev.mysql.com/doc/refman/5.0/en/load-data.html            

OR：常用 where中                        

ORDER：常用
OUT: 三种类型的变量，in是局部变量，传值不传参，out为全局变量，但初始化时值必须为NULL，inout也是全局，但是通过传值来设置初始值。                   

OUTER:  外链接 outer join                   

OUTFILE: SELECT ... INTO OUTFILE writes the selected rows to a file.
PRECISION: 精密匹配？               

PRIMARY：primary key                   

PROCEDURE：CREATE PROCEDURE, it can be called later http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
PURGE：清除logThe PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html                   

RAID0： RAID是一种存储技术http://en.wikipedia.org/wiki/RAID， 按照我的理解，RAID 0应该是相对于操作系统层的和存储相关的命名。它不是一个专有名词，而是约定熟成的RAID的某个scheme。              

RANGE：
READ：                    

READS ：                    

REAL：相当于DOUBLE PRECISION
REFERENCES：定义foreign key时使用         

REGEXP：用于正则表达式匹配字符串

http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp                    

RELEASE：
RENAME ：修改表名、数据库名的时候用                 

REPEAT： 重复。。直到。。REPEAT...UNITL...END REPEAT 

http://dev.mysql.com/doc/refman/5.0/en/repeat-statement.html                   

REPLACE：1, REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for aPRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. 2, REPLACE(text_string, from_string, to_string)。
REQUIRE ： 用在GRANT中，用于限定用户，比如REQUIRE SSL

http://dev.mysql.com/doc/refman/5.0/en/grant.html               

RESTRICT: a reference option,  Rejects the delete or update operation for the parent table. http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html                  

RETURN: 就是你想到的那个常用意思。
REVOKE:   REVOKE语句允许系统管理员从MySQL账户撤销的特权。E.G. REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost'; http://dev.mysql.com/doc/refman/4.1/en/revoke.html               

RIGHT：1，right join; 2 ,从右边开始截取字符串                     

RLIKE：Synonym for REGEXP 用正则表达式匹配
SCHEMA： CREATE SCHEMA is a synonym for CREATE DATABASE as of MySQL 5.0.2.                 

SCHEMAS： SHOW SCHEMAS等同于SHOW DATABASES                  

SECOND_MICROSECOND：

    SELECT DATE_ADD('1992-12-31 23:59:59.000002',INTERVAL '1.999999' SECOND_MICROSECOND);

SELECT：常用                  

SENSITIVE：                 

SEPARATOR：在GROUP_CONCAT中使用，这个function的作用是将查询到的记录合并成一个string, separator来定义分隔字符。http://mahmudahsan.wordpress.com/2008/08/27/mysql-the-group_concat-function/
SET：  常用                   

SHOW： 常用http://dev.mysql.com/doc/refman/5.5/en/show.html                     

SMALLINT：The signed range is –32768 to 32767.
SPATIAL：  用于定义index http://dev.mysql.com/doc/refman/5.0/en/create-index.html             

SPECIFIC：                  

SQL：
SQLEXCEPTION：SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'. http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html            

SQLSTATE：  看上一条。                

SQLWARNING：SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'
SQL_BIG_RESULT：   SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. This should not normally be needed. http://dev.mysql.com/doc/refman/5.1/en/select.html       

SQL_CALC_FOUND_ROWS：  SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result set, disregarding any LIMIT clause.  SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again.  http://dev.mysql.com/doc/refman/5.5/en/information-functions.html    

SQL_SMALL_RESULT：http://dev.mysql.com/doc/refman/5.1/en/select.html
SSL：就是你知道的那个SSL                    

STARTING：                  

STRAIGHT_JOIN：STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. 
TABLE：常用                   

TERMINATED：

   例子：

    SELECT * INTO OUTFILE 'data.txt'

  FIELDS TERMINATED BY ',' 

  FROM table2;               

THEN：WHEN...THEN
TINYBLOB：L + 1 bytes, where L < 2⁸                

TINYINT：   -128 ~ 127                

TINYTEXT：L + 1 bytes, where L < 2⁸
TO：                      

TRAILING：  TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM ] str), Indicates that only trailing prefixes are to be removed.                

TRIGGER：A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table.
TRUE：你懂的                    

UNDO：这个.....                     

UNION：UNION is used to combine the result from multiple SELECT statements into a single result set.
UNIQUE ：  你懂的              

UNLOCK：  UNLOCK TABLE http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html                  

UNSIGNED：用于非负数定义
UPDATE：你懂的                  

USAGE： Synonym for “no privileges”。 USAGE can be specified to create a user that has no privileges, or to specify the REQUIRE or WITH clauses for an account without changing its existing privileges.                    

USE： use database_name
USING：The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

http://dev.mysql.com/doc/refman/5.0/en/join.html

UTC_DATE:  UTC - 世界标准时间  return  'YYYY-MM-DD'         

UTC_TIME: returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format depending on the context of the function
UTC_TIMESTAMP: return 'YYYY-MM-DD HH:MM:SS'          

VALUES: 你懂的                    

VARBINARY：The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary strings rather than nonbinary strings. 
VARCHAR：你懂的                 

VARCHARACTER： 貌似没有             

VARYING：VARCHAR是CHARACTER VARYING一个缩写
WHEN：你懂的                    

WHERE ：你懂的                    

WHILE：循环，你懂的
WITH：   据说 WITH AS 用于缩短select时间，但是没在MYSQL上找到相关文档               

WRITE：                     

X509: GRANT数据库操作权限给用户的时候，REQUIRE的选项，和SSL相似
XOR:      Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is nonzero, otherwise 0 is returned.

http://dev.mysql.com/doc/refman/5.0/en/logical-operators.html               

YEAR_MONTH:

SELECT EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03'); 

 -> 200907               

ZEROFILL: 等同于 UNSIGNED。If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.