Shulou(Shulou.com)05/31 Report--

How to understand the keywords and reserved words in MySQL 5.7. aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Recently, I encountered some problems during the migration of data from Oracle to MySQL, including keywords.

The keywords of MySQL and Oracle are not the same. In the Oracle database, a large number of code fields are defined in our data table to represent the primary key, but code is the keyword in MySQL.

Let's take a look at keywords and reserved words in MySQL.

What are keywords and reserved words

Keywords are words that have meaning in SQL. Some keywords, such as SELECT,DELETE or BIGINT, are reserved and require special handling to be used as identifiers such as table and column names. This also applies to the names of built-in functions.

How to use keywords and reserved words

Unreserved keywords are allowed as identifiers without quotation marks. If you want to apply reserved words as identifiers, you must apply quotation marks.

For example, BEGIN and END are keywords, but not reserved words, so they don't need quotation marks as identifiers. INTERVAL is a reserved keyword and must be quoted before it can be used as an identifier.

Table 10.2 Keywords and Reserved Words in MySQL 5.7

ACCESSIBLE (R) account [a] ACTION

ADD (R) AFTERAGAINST

AGGREGATEALGORITHMALL (R)

ALTER (R) Always [b] ANALYSE

ANALYZE (R) AND (R) ANY

AS (R) ASC (R) ASCII

ASENSITIVE (R) ATAUTOEXTEND_SIZE

AUTO_INCREMENTAVGAVG_ROW_LENGTH

BACKUPBEFORE (R) BEGIN

BETWEEN (R) BIGINT (R) BINARY

BINLOGBITBLOB (R)

BLOCKBOOLBOOLEAN

BOTH (R) BTREEBY (R)

BYTECACHECALL (R)

CASCADE (R) CASCADEDCASE (R)

CATALOG_NAMECHAINCHANGE (R)

CHANGEDCHANNELL [c] CHAR (R)

CHARACTER (R) CHARSETCHECK (R)

CHECKSUMCIPHERCLASS_ORIGIN

CLIENTCLOSECOALESCE

CODECOLLATE (R) COLLATION

COLUMN (R) COLUMNSCOLUMN_FORMAT

COLUMN_NAMECOMMENTCOMMIT

COMMITTEDCOMPACTCOMPLETION

COMPRESSEDCOMPRESSION[d] CONCURRENT

CONDITION (R) CONNECTIONCONSISTENT

CONSTRAINT (R) CONSTRAINT_CATALOGCONSTRAINT_NAME

CONSTRAINT_SCHEMACONTAINSCONTEXT

CONTINUE (R) CONVERT (R) CPU

CREATE (R) CROSS (R) CUBE

CURRENTCURRENT_DATE (R) CURRENT_TIME (R)

CURRENT_TIMESTAMP (R) CURRENT_USER (R) CURSOR

CURSOR_NAMEDATADATABASE (R)

DATABASES (R) DATAFILEDATE

DATETIMEDAYDAY_HOUR (R)

DAY_MICROSECOND (R) DAY_MINUTE (R) DAY_SECOND

DEALLOCATEDEC (R) DECIMAL (R)

DECLARE (R) DEFAULT (R) DEFAULT_AUTH

DEFINERDELAYED (R) DELAY_KEY_WRITE

DELETE (R) DESC (R) DESCRIBE

DES_KEY_FILEDETERMINISTIC (R) DIAGNOSTICS

DIRECTORYDISABLEDISCARD

DISKDISTINCT (R) DISTINCTROW (R)

DIV (R) DODOUBLE (R)

DROP (R) DUAL (R) DUMPFILE

DUPLICATEDYNAMICEACH (R)

ELSE (R) ELSEIF (R) ENABLE

ENCLOSED (R) encryption [e] END

ENDSENGINEENGINES

ENUMERRORERRORS

ESCAPEESCAPED (R) EVENT

EVENTSEVERYEXCHANGE

EXECUTEEXISTS (R) EXIT (R)

EXPANSIONEXPIREEXPLAIN (R)

EXPORTEXTENDEDEXTENT_SIZE

FALSE (R) FASTFAULTS

FETCH (R) FIELDSFILE

FILE_BLOCK_ SIZE[f] FILTER[g] FIRST

FIXEDFLOAT (R) FLOAT4 (R)

FLOAT8 (R) FLUSHFOLLOWS [h]

FOR (R) FORCE (R) FOREIGN

FORMATFOUNDFROM (R)

FULLFULLTEXT (R) FUNCTION

GENERALGENERATED [I] (R) GEOMETRY

GEOMETRYCOLLECTIONGET (R) GET_FORMAT

GLOBALGRANT (R) GRANTS

GROUP (R) GROUP_ replication [j] HANDLER

HASHHAVING (R) HELP

HIGH_PRIORITY (R) HOSTHOSTS

HOURHOUR_MICROSECOND (R) HOUR_MINUTE (R)

HOUR_SECOND (R) IDENTIFIEDIF (R)

IGNORE (R) IGNORE_SERVER_IDSIMPORT

IN (R) INDEX (R) INDEXES

INFILE (R) INITIAL_SIZEINNER (R)

INOUT (R) INSENSITIVE (R) INSERT

INSERT_ METHODINSTALLINSTANCE[k]

INT (R) INT1 (R) INT2

INT3 (R) INT4 (R) INT8

INTEGER (R) INTERVAL (R) INTO

INVOKERIOIO_AFTER_GTIDS (R)

IO_BEFORE_GTIDS (R) IO_THREADIPC

IS (R) ISOLATIONISSUER

ITERATE (R) JOIN (R) JSON [l]

KEY (R) KEYS (R) KEY_BLOCK_SIZE

KILL (R) LANGUAGELAST

LEADING (R) LEAVE (R) LEAVES

LEFT (R) LESSLEVEL

LIKE (R) LIMIT (R) LINEAR

LINES (R) LINESTRINGLIST

LOAD (R) LOCALLOCALTIME (R)

LOCALTIMESTAMP (R) LOCK (R) LOCKS

LOGFILELOGSLONG (R)

LONGBLOB (R) LONGTEXT (R) LOOP

LOW_PRIORITY (R) MASTERMASTER_AUTO_POSITION

MASTER_BIND (R) MASTER_CONNECT_RETRYMASTER_DELAY

MASTER_HEARTBEAT_PERIODMASTER_HOSTMASTER_LOG_FILE

MASTER_LOG_POSMASTER_PASSWORDMASTER_PORT

MASTER_RETRY_COUNTMASTER_SERVER_IDMASTER_SSL

MASTER_SSL_CAMASTER_SSL_CAPATHMASTER_SSL_CERT

MASTER_SSL_CIPHERMASTER_SSL_CRLMASTER_SSL_CRLPATH

MASTER_SSL_KEYMASTER_SSL_VERIFY_SERVER_CERT (R) MASTER_TLS_ version [m]

MASTER_USERMATCH (R) MAXVALUE (R)

MAX_CONNECTIONS_PER_HOURMAX_QUERIES_PER_HOURMAX_ROWS

MAX_SIZEMAX_STATEMENT_ TIME[n] MAX_UPDATES_PER_HOUR

MAX_USER_CONNECTIONSMEDIUMMEDIUMBLOB (R)

MEDIUMINT (R) MEDIUMTEXT (R) MEMORY

MERGEMESSAGE_TEXTMICROSECOND

MIDDLEINT (R) MIGRATEMINUTE

MINUTE_MICROSECOND (R) MINUTE_SECOND (R) MIN_ROWS

MOD (R) MODEMODIFIES (R)

MODIFYMONTHMULTILINESTRING

MULTIPOINTMULTIPOLYGONMUTEX

MYSQL_ERRNONAMENAMES

NATIONALNATURAL (R) NCHAR

NDBNDBCLUSTERNEVER [o]

NEWNEXTNO

NODEGROUPNONBLOCKING[p] NONE

NOT (R) NO_WAITNO_WRITE_TO_BINLOG (R)

NULL (R) NUMBERNUMERIC (R)

NVARCHAROFFSETOLD_ PASSWORD[q]

ON (R) ONEONLY

OPENOPTIMIZE (R) OPTIMIZER_ costs [r] (R)

OPTION (R) OPTIONALLY (R) OPTIONS

OR (R) ORDER (R) OUT

OUTER (R) OUTFILE (R) OWNER

PACK_KEYSPAGEPARSER

PARSE_GCOL_ Express [s] PARTIALPARTITION (R)

PARTITIONINGPARTITIONSPASSWORD

PHASEPLUGINPLUGINS

PLUGIN_DIRPOINTPOLYGON

PORTPRECEDES [t] PRECISION (R)

PREPAREPRESERVEPREV

PRIMARY (R) PRIVILEGESPROCEDURE (R)

PROCESSLISTPROFILEPROFILES

PROXYPURGE (R) QUARTER

QUERYQUICKRANGE (R)

READ (R) READS (R) READ_ONLY

READ_WRITE (R) REAL (R) REBUILD

RECOVERREDOFILEREDO_BUFFER_SIZE

REDUNDANTREFERENCES (R) REGEXP (R)

RELAYRELAYLOGRELAY_LOG_FILE

RELAY_LOG_POSRELAY_THREADRELEASE (R)

RELOADREMOVERENAME (R)

REORGANIZEREPAIRREPEAT (R)

REPEATABLEREPLACE (R) REPLICATE_DO_ DB [u]

REPLICATE_DO_ TABLE[v] REPLICATE_IGNORE_ DB[w] REPLICATE_IGNORE_ TABLE[x]

REPLICATE_REWRITE_ DB[y] REPLICATE_WILD_DO_ TABLE[z] REPLICATE_WILD_IGNORE_ TABLE[aa]

REPLICATIONREQUIRE (R) RESET

RESIGNAL (R) RESTORERESTRICT (R)

RESUMERETURN (R) RETURNED_SQLSTATE

RETURNSREVERSEREVOKE (R)

RIGHT (R) RLIKE (R) ROLLBACK

ROLLUPROTATE[ab] ROUTINE

ROWROWSROW_COUNT

ROW_FORMATRTREESAVEPOINT

SCHEDULESCHEMA (R) SCHEMAS (R)

SCHEMA_NAMESECONDSECOND_MICROSECOND (R)

SECURITYSELECT (R) SENSITIVE (R)

SEPARATOR (R) SERIALSERIALIZABLE

SERVERSESSIONSET (R)

SHARESHOW (R) SHUTDOWN

SIGNAL (R) SIGNEDSIMPLE

SLAVESLOWSMALLINT (R)

SNAPSHOTSOCKETSOME

SONAMESOUNDSSOURCE

SPATIAL (R) SPECIFIC (R) SQL

SQLEXCEPTION (R) SQLSTATE (R) SQLWARNING

SQL_AFTER_GTIDSSQL_AFTER_MTS_GAPSSQL_BEFORE_GTIDS

SQL_BIG_RESULT (R) SQL_BUFFER_RESULTSQL_CACHE

SQL_CALC_FOUND_ROWS (R) SQL_NO_CACHESQL_SMALL_RESULT (R)

SQL_THREADSQL_TSI_DAYSQL_TSI_HOUR

SQL_TSI_MINUTESQL_TSI_MONTHSQL_TSI_QUARTER

SQL_TSI_SECONDSQL_TSI_WEEKSQL_TSI_YEAR

SSL (R) STACKEDSTART

STARTING (R) STARTSSTATS_AUTO_RECALC

STATS_PERSISTENTSTATS_SAMPLE_PAGESSTATUS

STOPSTORAGESTORED [ac] (R)

STRAIGHT_JOIN (R) STRINGSUBCLASS_ORIGIN

SUBJECTSUBPARTITIONSUBPARTITIONS

SUPERSUSPENDSWAPS

SWITCHESTABLE (R) TABLES

TABLESPACETABLE_CHECKSUMTABLE_NAME

TEMPORARYTEMPTABLETERMINATED (R)

TEXTTHANTHEN (R)

TIMETIMESTAMPTIMESTAMPADD

TIMESTAMPDIFFTINYBLOB (R) TINYINT (R)

TINYTEXT (R) TO (R) TRAILING

TRANSACTIONTRIGGER (R) TRIGGERS

TRUE (R) TRUNCATETYPE

TYPESUNCOMMITTEDUNDEFINED

UNDO (R) UNDOFILEUNDO_BUFFER_SIZE

UNICODEUNINSTALLUNION (R)

UNIQUE (R) UNKNOWNUNLOCK (R)

UNSIGNED (R) UNTILUPDATE (R)

UPGRADEUSAGE (R) USE (R)

USERUSER_RESOURCESUSE_FRM

USING (R) UTC_DATE (R) UTC_TIME

UTC_TIMESTAMP (R) value [ad] VALUE

VALUES (R) VARBINARY (R) VARCHAR

VARCHARACTER (R) VARIABLESVARYING (R)

VIEWVIRTUAL [ae] (R) WAIT

WARNINGSWEEKWEIGHT_STRING

WHEN (R) WHERE (R) WHILE

WITH (R) with [af] WORK

WRAPPERWRITE (R) X509

XAXID[ag] XML

XOR (R) YEARYEAR_MONTH (R)

ZEROFILL (R)

[a] ACCOUNT: added in 5.7.6 (nonreserved)

[B] ALWAYS: added in 5.7.6 (nonreserved)

[C] CHANNEL: added in 5.7.6 (nonreserved)

[d] COMPRESSION: added in 5.7.8 (nonreserved)

[e] ENCRYPTION: added in 5.7.11 (nonreserved)

[F] FILE_BLOCK_SIZE: added in 5.7.6 (nonreserved)

[G] FILTER: added in 5.7.3 (nonreserved)

[h] FOLLOWS: added in 5.7.2 (nonreserved)

[I] GENERATED: added in 5.7.6 (reserved)

[J] GROUP_REPLICATION: added in 5.7.6 (nonreserved)

[K] INSTANCE: added in 5.7.11 (nonreserved)

[l] JSON: added in 5.7.8 (nonreserved)

[M] MASTER_TLS_VERSION: added in 5.7.10 (nonreserved)

[n] MAX_STATEMENT_TIME: added in 5.7.4 (nonreserved); removed in 5.7.8

[o] NEVER: added in 5.7.4 (nonreserved)

[p] NONBLOCKING: removed in 5.7.6

[q] OLD_PASSWORD: removed in 5.7.5

[r] OPTIMIZER_COSTS: added in 5.7.5 (reserved)

[s] PARSE_GCOL_EXPR: added in 5.7.6 (reserved); became nonreserved in 5.7.8

[t] PRECEDES: added in 5.7.2 (nonreserved)

[U] REPLICATE_DO_DB: added in 5.7.3 (nonreserved)

[v] REPLICATE_DO_TABLE: added in 5.7.3 (nonreserved)

[W] REPLICATE_IGNORE_DB: added in 5.7.3 (nonreserved)

[X] REPLICATE_IGNORE_TABLE: added in 5.7.3 (nonreserved)

[y] REPLICATE_REWRITE_DB: added in 5.7.3 (nonreserved)

[Z] REPLICATE_WILD_DO_TABLE: added in 5.7.3 (nonreserved)

[aa] REPLICATE_WILD_IGNORE_TABLE: added in 5.7.3 (nonreserved)

[ab] ROTATE: added in 5.7.11 (nonreserved)

[ac] STORED: added in 5.7.6 (reserved)

[ad] VALIDATION: added in 5.7.5 (nonreserved)

[ae] VIRTUAL: added in 5.7.6 (reserved)

[af] WITHOUT: added in 5.7.5 (nonreserved)

[ag] XID: added in 5.7.5 (nonreserved)

Table 10.3 Keywords and Reserved Words Added in MySQL 5.7 Compared to MySQL 5.6

ACCOUNTALWAYSCHANNELCOMPRESSIONENCRYPTIONFILE_BLOCK_SIZEFILTERFOLLOWSGENERATED (R) GROUP_REPLICATIONINSTANCEJSONMASTER_TLS_VERSIONNEVEROPTIMIZER_COSTS (R) PARSE_GCOL_EXPRPRECEDESREPLICATE_DO_DBREPLICATE_DO_TABLEREPLICATE_IGNORE_DBREPLICATE_IGNORE_TABLEREPLICATE_REWRITE_DBREPLICATE_WILD_DO_TABLEREPLICATE_WILD_IGNORE_TABLEROTATESTACKEDSTORED (R) VALIDATIONVIRTUAL (R) WITHOUTXID's answer to the question on how to understand the keywords and reserved words in MySQL 5.7.I hope the above content can be of some help to you, if you still have a lot of questions unsolved. You can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.