搜索到
12
篇与
MySQL
的结果
-
MySQL远程连接的一些总结 前言MySQL是目前非常流行的数据库之一，也是中小企业持久化存储的首选数据库。不同于我们日常学习，在实际应用中，MySQL服务都会挂载在某台服务器上。如果MySQL部署在某台云服务器上，这样一来，操纵数据库每次都需要先连接服务器，再进入数据库操作，不是很方便。于是，学习远程连接 MySQL 的方法是数据库在服务器上时的必修课。但为了安全考虑，MySQL的远程连接并不是一键容易事，特别是在MySQL8.x版本时，下面会介绍到。准备工作在这个阶段，确保你已经进入了你想要远程访问的数据库服务中，通常的界面如下：放行远程主机访问通常来说，MySQL只允许用户在本地主机访问。通过查询 user 表，也可以看到允许的主机信息：select host,user from mysql.userhost 字段下面的 localhost 代表只接受本地主机。所以，我们的工作就很清晰了，就是修改访问权限信息，使得指定的用户能够接受远程访问。通常来说，我们有两种方式实现这个效果。第一种：改账号权限（建议）既然远程主机没有权限连接，我们可以通过 GRANT 语句修改指定用户的权限。GRANT 语句的语法大致为：GRANT 权限 ON 数据库对象 TO 用户。授予对所有数据库操作的所有权限给任何主机（’%’）访问的 root 用户 ，这样任何连接到该数据库的root用户的能访问所有信息。GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION; 刷新权限：flush privileges;再次查询，可以发现会新增一条记录，host 字段下的 % 表示任何主机：如果只是想授予某个单独的主机访问权限，则 % 可以修改为指定的 IP：GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.16.28.71' IDENTIFIED BY '你的密码' WITH GRANT OPTION; 效果类似：刷新权限：flush privileges;第二种：改表法使用 GRANT 语句的方法是直接添加一条新记录，我个人建议按上面的方式做。但是，我们同样可以采用直接修改 user 为 root 的host 字段内容，直接使用 UPDATE 语句修改表：UPDATE mysql.user SET host = '%' WHERE user = 'root';刷新权限：flush privileges;效果如图，直接修改 host 字段值，而不是添加。到这一步，大多数情况下，我们就可以使用数据库工具如：Web SQLyog、Navicat、Dbeaver等等连接上我们的远程数据库了。MySQL 8.x 的注意点但请注意，如果你的 MySQL 版本是 8.x的话，由于密码加密方式的不同，连接时可能会出现如下提示：因此，我们必须 修改加密方式 以实现远程连接，使用 ALTER 语句：ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';最后不要忘了刷新权限：flush privileges;OK，教程整体到这里就结束了，如果遇到什么问题，欢迎大家在评论区发表看法参考mysql开启远程连接本文作者： 千帆过烬本文链接： https://qianfanguojin.top/2021/06/20/关于MySQL的远程连接的一些总结/ -
MySQL创建索引 -- 创建主键 ：主键默认为唯一索引ALTER TABLE test_user ADD PRIMARY KEY ( id )-- 创建普通索引CREATE INDEX idx_name ON table_name (column_name);-- 创建唯一索引CREATE UNIQUE INDEX uk_name ON table_name (column_name)MySQL中，使用 FORCE INDEX () 语句进行强制索引查询SELECT * FROM salaries FORCE INDEX (idx_emp_no) WHERE emp_no = 100052种方法①用CREATE②用ALTERCREATE INDEX 索引名称 ON 表名(字段); ALTER TABLE 表名 ADD INDEX 索引名称(字段);假如有一张表 TB字段有a b c d用 CREATE 方法来创建 CREATE INDEX index_a ON TB(a); 用 ALTER 来创建 ALTER TABLE TB ADD INDEX index_a(a);联合索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )全文索引ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 唯一索引ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 主键索引ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 查看索引:SHOW INDEX FROM `table_name`;
-
MySQL升级8.0的新故障，utf8mb4_0900_ai_ci是啥？ 前段时间，遇到朋友的求助，说以前运行的好好的系统现在出问题了，而且看不懂报错到底是什么意思。我仔细看看报错信息，应该是MySQL数据库报出来的，大意是说：collation不兼容，一个是 utf8mb4_0900_ai_ci，另一个是utf8mb4_general_ci。utf8mb4_general_ci这玩意儿我见过，是针对utf8mb4编码的collation，但是utf8mb4_0900_ai_ci是啥，我也没见过。于是我问他，这玩意儿从哪里出来的？他说：“我也不知道，我完全没见过啊。再说，我数据库编码已经是utf8mb4了，怎么还会有这么多名堂？”看他着急又不知所措的样子，我便花了点时间来研究，还真学到点新知识。而且我也发现，有许多程序员天真的以为“用了UTF8就等于做了国际化了，不用再担心编码问题”。看来，这个话题还真值得多讲讲。首先从utf8mb4_0900_ai_ci这个诡异的名字说起。Unicode编码的诞生，是为了解决之前各国的计算机文字编码自成一体的问题。不同国家采用不同的编码，自己用还算正常，但是跨文化交流必然会出问题，更无法解决“在同一篇文档里又要显示中文又要显示韩文还要显示日文”之类的问题。有了Unicode，地球上所有的文字都有独一无二的编码（Code Point，也就是为它分配的码值，或者说“逻辑代号”），前述问题就解决了。但是Unicode（有个相关的名字是UCS，Universal Coded Character Set，二者基本等价）只确定了码值，或者说，只分配了逻辑代号。至于这些逻辑代号在实际使用中如何存储，如何传输，那是另一个问题。而UTF-8，就是解决存储和传输等问题的“实际方案”。实际上，UTF的全名是Unicode Transformation Format，也就是“Unicode变换格式”。这里的“变换”，基本可以类比为：要告诉别人明天早上九点来开会，到底是发邮件呢，还是打电话呢，还是写纸条呢，还是直接去敲门打招呼呢？。所以，Unicode的变换格式不只UTF-8一种，还有UTF-16、UTF-32等等。UTF-8使用比较普遍，因为它是变长编码，如果只传输ASCII字符，则每个字符只需要一个字节。因此，如果数据中包含大量的ASCII字符，那么UTF-8可以节省很多存储空间。老一点的程序员大概都知道UTF-8，在MySQL中写作utf8，没有横线。如果要用MySQL存储多种语言的字符，那么把字符集（character set）设定为utf8是合适的选择。注意，MySQL中必须指定utf8，而不是Unicode。因为Unicode只是逻辑规范，utf8才是具体存储和传输的格式。那么，utf8mb4_0900_ai_ci什么意思呢？我们分部分来看这个名字，先从开头看起。utf8mb4，这个名字许多人大概熟悉。如今
-
MySQL 8.0 关键字和保留字 关键字是在 SQL 中具有重要意义的词。某些关键字，如SELECT， DELETE或 BIGINT，被保留，需要用作标识符，例如表和列名特殊待遇。对于内置函数的名称，这也可能是正确的。允许非保留关键字作为标识符而无需引用。如果您按照第 9.2 节“架构对象名称”中的描述引用保留字，则允许将其用作标识符：mysql> CREATE TABLE interval (begin INT, end INT);ERROR 1064 (42000): You have an error in your SQL syntax ...near 'interval (begin INT, end INT)'BEGINandEND是关键字但不保留，因此它们用作标识符不需要引用。INTERVAL是保留关键字，必须加引号才能用作标识符：mysql> CREATE TABLE interval (begin INT, end INT);Query OK, 0 rows affected (0.01 sec)例外：限定名称中的句点后面的单词必须是标识符，因此即使它被保留也不需要引用：mysql> CREATE TABLE mydb.interval (begin INT, end INT);Query OK, 0 rows affected (0.01 sec)允许将内置函数的名称用作标识符，但可能需要谨慎使用。例如， COUNT可以作为列名。但是，默认情况下，函数名和后续( 字符之间的函数调用中不允许有空格。此要求使解析器能够区分名称是在函数调用中还是在非函数上下文中使用。有关识别函数名称的更多详细信息，请参阅 第 9.2.5 节，“函数名称解析和解析”。该INFORMATION_SCHEMA.KEYWORDS表列出了 MySQL 认为是关键字的词，并指出它们是否被保留。见第 26.3.17 节，“INFORMATION_SCHEMA 关键字表”。MySQL 8.0 关键字和保留字MySQL 8.0 新关键字和保留字MySQL 8.0 删除关键字和保留字MySQL 8.0 关键字和保留字以下列表显示了 MySQL 8.0 中的关键字和保留字，以及各个单词在不同版本之间的变化。保留关键字用 (R) 标记。此外，_FILENAME被保留。在某些时候，您可能会升级到更高版本，因此最好也查看一下未来的保留字。您可以在涵盖更高版本 MySQL 的手册中找到这些内容。列表中的大多数保留字都被标准 SQL 禁止作为列名或表名（例如， GROUP）。有一些是保留的，因为 MySQL 需要它们并使用yacc解析器。A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | ZAACCESSIBLE (R)ACCOUNTACTIONACTIVE; added in 8.0.14 (nonreserved)ADD (R)ADMIN; became nonreserved in 8.0.12AFTERAGAINSTAGGREGATEALGORITHMALL (R)ALTER (R)ALWAYSANALYSE; removed in 8.0.1ANALYZE (R)AND (R)ANYARRAY; added in 8.0.17 (reserved); became nonreserved in 8.0.19AS (R)ASC (R)ASCIIASENSITIVE (R)ATATTRIBUTE; added in 8.0.21 (nonreserved)AUTHENTICATION; added in 8.0.27 (nonreserved)AUTOEXTEND_SIZEAUTO_INCREMENTAVGAVG_ROW_LENGTHBBACKUPBEFORE (R)BEGINBETWEEN (R)BIGINT (R)BINARY (R)BINLOGBITBLOB (R)BLOCKBOOLBOOLEANBOTH (R)BTREEBUCKETS; added in 8.0.2 (nonreserved)BY (R)BYTECCACHECALL (R)CASCADE (R)CASCADEDCASE (R)CATALOG_NAMECHAINCHALLENGE_RESPONSE; added in 8.0.27 (nonreserved)CHANGE (R)CHANGEDCHANNELCHAR (R)CHARACTER (R)CHARSETCHECK (R)CHECKSUMCIPHERCLASS_ORIGINCLIENTCLONE; added in 8.0.3 (nonreserved)CLOSECOALESCECODECOLLATE (R)COLLATIONCOLUMN (R)COLUMNSCOLUMN_FORMATCOLUMN_NAMECOMMENTCOMMITCOMMITTEDCOMPACTCOMPLETIONCOMPONENTCOMPRESSEDCOMPRESSIONCONCURRENTCONDITION (R)CONNECTIONCONSISTENTCONSTRAINT (R)CONSTRAINT_CATALOGCONSTRAINT_NAMECONSTRAINT_SCHEMACONTAINSCONTEXTCONTINUE (R)CONVERT (R)CPUCREATE (R)CROSS (R)CUBE (R); became reserved in 8.0.1CUME_DIST (R); added in 8.0.2 (reserved)CURRENTCURRENT_DATE (R)CURRENT_TIME (R)CURRENT_TIMESTAMP (R)CURRENT_USER (R)CURSOR (R)CURSOR_NAMEDDATADATABASE (R)DATABASES (R)DATAFILEDATEDATETIMEDAYDAY_HOUR (R)DAY_MICROSECOND (R)DAY_MINUTE (R)DAY_SECOND (R)DEALLOCATEDEC (R)DECIMAL (R)DECLARE (R)DEFAULT (R)DEFAULT_AUTHDEFINERDEFINITION; added in 8.0.4 (nonreserved)DELAYED (R)DELAY_KEY_WRITEDELETE (R)DENSE_RANK (R); added in 8.0.2 (reserved)DESC (R)DESCRIBE (R)DESCRIPTION; added in 8.0.4 (nonreserved)DES_KEY_FILE; removed in 8.0.3DETERMINISTIC (R)DIAGNOSTICSDIRECTORYDISABLEDISCARDDISKDISTINCT (R)DISTINCTROW (R)DIV (R)DODOUBLE (R)DROP (R)DUAL (R)DUMPFILEDUPLICATEDYNAMICEEACH (R)ELSE (R)ELSEIF (R)EMPTY (R); added in 8.0.4 (reserved)ENABLEENCLOSED (R)ENCRYPTIONENDENDSENFORCED; added in 8.0.16 (nonreserved)ENGINEENGINESENGINE_ATTRIBUTE; added in 8.0.21 (nonreserved)ENUMERRORERRORSESCAPEESCAPED (R)EVENTEVENTSEVERYEXCEPT (R)EXCHANGEEXCLUDE; added in 8.0.2 (nonreserved)EXECUTEEXISTS (R)EXIT (R)EXPANSIONEXPIREEXPLAIN (R)EXPORTEXTENDEDEXTENT_SIZEFFACTOR; added in 8.0.27 (nonreserved)FAILED_LOGIN_ATTEMPTS; added in 8.0.19 (nonreserved)FALSE (R)FASTFAULTSFETCH (R)FIELDSFILEFILE_BLOCK_SIZEFILTERFINISH; added in 8.0.27 (nonreserved)FIRSTFIRST_VALUE (R); added in 8.0.2 (reserved)FIXEDFLOAT (R)FLOAT4 (R)FLOAT8 (R)FLUSHFOLLOWING; added in 8.0.2 (nonreserved)FOLLOWSFOR (R)FORCE (R)FOREIGN (R)FORMATFOUNDFROM (R)FULLFULLTEXT (R)FUNCTION (R); became reserved in 8.0.1GGENERALGENERATED (R)GEOMCOLLECTION; added in 8.0.11 (nonreserved)GEOMETRYGEOMETRYCOLLECTIONGET (R)GET_FORMATGET_MASTER_PUBLIC_KEY; added in 8.0.4 (reserved); became nonreserved in 8.0.11GET_SOURCE_PUBLIC_KEY; added in 8.0.23 (nonreserved)GLOBALGRANT (R)GRANTSGROUP (R)GROUPING (R); added in 8.0.1 (reserved)GROUPS (R); added in 8.0.2 (reserved)GROUP_REPLICATIONGTID_ONLY; added in 8.0.27 (nonreserved)HHANDLERHASHHAVING (R)HELPHIGH_PRIORITY (R)HISTOGRAM; added in 8.0.2 (nonreserved)HISTORY; added in 8.0.3 (nonreserved)HOSTHOSTSHOURHOUR_MICROSECOND (R)HOUR_MINUTE (R)HOUR_SECOND (R)IIDENTIFIEDIF (R)IGNORE (R)IGNORE_SERVER_IDSIMPORTIN (R)INACTIVE; added in 8.0.14 (nonreserved)INDEX (R)INDEXESINFILE (R)INITIAL; added in 8.0.27 (nonreserved)INITIAL_SIZEINITIATE; added in 8.0.27 (nonreserved)INNER (R)INOUT (R)INSENSITIVE (R)INSERT (R)INSERT_METHODINSTALLINSTANCEINT (R)INT1 (R)INT2 (R)INT3 (R)INT4 (R)INT8 (R)INTEGER (R)INTERVAL (R)INTO (R)INVISIBLEINVOKERIOIO_AFTER_GTIDS (R)IO_BEFORE_GTIDS (R)IO_THREADIPCIS (R)ISOLATIONISSUERITERATE (R)JJOIN (R)JSONJSON_TABLE (R); added in 8.0.4 (reserved)JSON_VALUE; added in 8.0.21 (nonreserved)KKEY (R)KEYRING; added in 8.0.24 (nonreserved)KEYS (R)KEY_BLOCK_SIZEKILL (R)LLAG (R); added in 8.0.2 (reserved)LANGUAGELASTLAST_VALUE (R); added in 8.0.2 (reserved)LATERAL (R); added in 8.0.14 (reserved)LEAD (R); added in 8.0.2 (reserved)LEADING (R)LEAVE (R)LEAVESLEFT (R)LESSLEVELLIKE (R)LIMIT (R)LINEAR (R)LINES (R)LINESTRINGLISTLOAD (R)LOCALLOCALTIME (R)LOCALTIMESTAMP (R)LOCK (R)LOCKED; added in 8.0.1 (nonreserved)LOCKSLOGFILELOGSLONG (R)LONGBLOB (R)LONGTEXT (R)LOOP (R)LOW_PRIORITY (R)MMASTERMASTER_AUTO_POSITIONMASTER_BIND (R)MASTER_COMPRESSION_ALGORITHMS; added in 8.0.18 (nonreserved)MASTER_CONNECT_RETRYMASTER_DELAYMASTER_HEARTBEAT_PERIODMASTER_HOSTMASTER_LOG_FILEMASTER_LOG_POSMASTER_PASSWORDMASTER_PORTMASTER_PUBLIC_KEY_PATH; added in 8.0.4 (nonreserved)MASTER_RETRY_COUNTMASTER_SERVER_ID; removed in 8.0.23MASTER_SSLMASTER_SSL_CAMASTER_SSL_CAPATHMASTER_SSL_CERTMASTER_SSL_CIPHERMASTER_SSL_CRLMASTER_SSL_CRLPATHMASTER_SSL_KEYMASTER_SSL_VERIFY_SERVER_CERT (R)MASTER_TLS_CIPHERSUITES; added in 8.0.19 (nonreserved)MASTER_TLS_VERSIONMASTER_USERMASTER_ZSTD_COMPRESSION_LEVEL; added in 8.0.18 (nonreserved)MATCH (R)MAXVALUE (R)MAX_CONNECTIONS_PER_HOURMAX_QUERIES_PER_HOURMAX_ROWSMAX_SIZEMAX_UPDATES_PER_HOURMAX_USER_CONNECTIONSMEDIUMMEDIUMBLOB (R)MEDIUMINT (R)MEDIUMTEXT (R)MEMBER; added in 8.0.17 (reserved); became nonreserved in 8.0.19MEMORYMERGEMESSAGE_TEXTMICROSECONDMIDDLEINT (R)MIGRATEMINUTEMINUTE_MICROSECOND (R)MINUTE_SECOND (R)MIN_ROWSMOD (R)MODEMODIFIES (R)MODIFYMONTHMULTILINESTRINGMULTIPOINTMULTIPOLYGONMUTEXMYSQL_ERRNONNAMENAMESNATIONALNATURAL (R)NCHARNDBNDBCLUSTERNESTED; added in 8.0.4 (nonreserved)NETWORK_NAMESPACE; added in 8.0.16 (nonreserved)NEVERNEWNEXTNONODEGROUPNONENOT (R)NOWAIT; added in 8.0.1 (nonreserved)NO_WAITNO_WRITE_TO_BINLOG (R)NTH_VALUE (R); added in 8.0.2 (reserved)NTILE (R); added in 8.0.2 (reserved)NULL (R)NULLS; added in 8.0.2 (nonreserved)NUMBERNUMERIC (R)NVARCHAROOF (R); added in 8.0.1 (reserved)OFF; added in 8.0.20 (nonreserved)OFFSETOJ; added in 8.0.16 (nonreserved)OLD; added in 8.0.14 (nonreserved)ON (R)ONEONLYOPENOPTIMIZE (R)OPTIMIZER_COSTS (R)OPTION (R)OPTIONAL; added in 8.0.13 (nonreserved)OPTIONALLY (R)OPTIONSOR (R)ORDER (R)ORDINALITY; added in 8.0.4 (nonreserved)ORGANIZATION; added in 8.0.4 (nonreserved)OTHERS; added in 8.0.2 (nonreserved)OUT (R)OUTER (R)OUTFILE (R)OVER (R); added in 8.0.2 (reserved)OWNERPPACK_KEYSPAGEPARSERPARTIALPARTITION (R)PARTITIONINGPARTITIONSPASSWORDPASSWORD_LOCK_TIME; added in 8.0.19 (nonreserved)PATH; added in 8.0.4 (nonreserved)PERCENT_RANK (R); added in 8.0.2 (reserved)PERSIST; became nonreserved in 8.0.16PERSIST_ONLY; added in 8.0.2 (reserved); became nonreserved in 8.0.16PHASEPLUGINPLUGINSPLUGIN_DIRPOINTPOLYGONPORTPRECEDESPRECEDING; added in 8.0.2 (nonreserved)PRECISION (R)PREPAREPRESERVEPREVPRIMARY (R)PRIVILEGESPRIVILEGE_CHECKS_USER; added in 8.0.18 (nonreserved)PROCEDURE (R)PROCESS; added in 8.0.11 (nonreserved)PROCESSLISTPROFILEPROFILESPROXYPURGE (R)QQUARTERQUERYQUICKRRANDOM; added in 8.0.18 (nonreserved)RANGE (R)RANK (R); added in 8.0.2 (reserved)READ (R)READS (R)READ_ONLYREAD_WRITE (R)REAL (R)REBUILDRECOVERRECURSIVE (R); added in 8.0.1 (reserved)REDOFILE; removed in 8.0.3REDO_BUFFER_SIZEREDUNDANTREFERENCE; added in 8.0.4 (nonreserved)REFERENCES (R)REGEXP (R)REGISTRATION; added in 8.0.27 (nonreserved)RELAYRELAYLOGRELAY_LOG_FILERELAY_LOG_POSRELAY_THREADRELEASE (R)RELOADREMOTE; added in 8.0.3 (nonreserved); removed in 8.0.14REMOVERENAME (R)REORGANIZEREPAIRREPEAT (R)REPEATABLEREPLACE (R)REPLICA; added in 8.0.22 (nonreserved)REPLICAS; added in 8.0.22 (nonreserved)REPLICATE_DO_DBREPLICATE_DO_TABLEREPLICATE_IGNORE_DBREPLICATE_IGNORE_TABLEREPLICATE_REWRITE_DBREPLICATE_WILD_DO_TABLEREPLICATE_WILD_IGNORE_TABLEREPLICATIONREQUIRE (R)REQUIRE_ROW_FORMAT; added in 8.0.19 (nonreserved)RESETRESIGNAL (R)RESOURCE; added in 8.0.3 (nonreserved)RESPECT; added in 8.0.2 (nonreserved)RESTART; added in 8.0.4 (nonreserved)RESTORERESTRICT (R)RESUMERETAIN; added in 8.0.14 (nonreserved)RETURN (R)RETURNED_SQLSTATERETURNING; added in 8.0.21 (nonreserved)RETURNSREUSE; added in 8.0.3 (nonreserved)REVERSEREVOKE (R)RIGHT (R)RLIKE (R)ROLE; became nonreserved in 8.0.1ROLLBACKROLLUPROTATEROUTINEROW (R); became reserved in 8.0.2ROWS (R); became reserved in 8.0.2ROW_COUNTROW_FORMATROW_NUMBER (R); added in 8.0.2 (reserved)RTREESSAVEPOINTSCHEDULESCHEMA (R)SCHEMAS (R)SCHEMA_NAMESECONDSECONDARY; added in 8.0.16 (nonreserved)SECONDARY_ENGINE; added in 8.0.13 (nonreserved)SECONDARY_ENGINE_ATTRIBUTE; added in 8.0.21 (nonreserved)SECONDARY_LOAD; added in 8.0.13 (nonreserved)SECONDARY_UNLOAD; added in 8.0.13 (nonreserved)SECOND_MICROSECOND (R)SECURITYSELECT (R)SENSITIVE (R)SEPARATOR (R)SERIALSERIALIZABLESERVERSESSIONSET (R)SHARESHOW (R)SHUTDOWNSIGNAL (R)SIGNEDSIMPLESKIP; added in 8.0.1 (nonreserved)SLAVESLOWSMALLINT (R)SNAPSHOTSOCKETSOMESONAMESOUNDSSOURCESOURCE_AUTO_POSITION; added in 8.0.23 (nonreserved)SOURCE_BIND; added in 8.0.23 (nonreserved)SOURCE_COMPRESSION_ALGORITHMS; added in 8.0.23 (nonreserved)SOURCE_CONNECT_RETRY; added in 8.0.23 (nonreserved)SOURCE_DELAY; added in 8.0.23 (nonreserved)SOURCE_HEARTBEAT_PERIOD; added in 8.0.23 (nonreserved)SOURCE_HOST; added in 8.0.23 (nonreserved)SOURCE_LOG_FILE; added in 8.0.23 (nonreserved)SOURCE_LOG_POS; added in 8.0.23 (nonreserved)SOURCE_PASSWORD; added in 8.0.23 (nonreserved)SOURCE_PORT; added in 8.0.23 (nonreserved)SOURCE_PUBLIC_KEY_PATH; added in 8.0.23 (nonreserved)SOURCE_RETRY_COUNT; added in 8.0.23 (nonreserved)SOURCE_SSL; added in 8.0.23 (nonreserved)SOURCE_SSL_CA; added in 8.0.23 (nonreserved)SOURCE_SSL_CAPATH; added in 8.0.23 (nonreserved)SOURCE_SSL_CERT; added in 8.0.23 (nonreserved)SOURCE_SSL_CIPHER; added in 8.0.23 (nonreserved)SOURCE_SSL_CRL; added in 8.0.23 (nonreserved)SOURCE_SSL_CRLPATH; added in 8.0.23 (nonreserved)SOURCE_SSL_KEY; added in 8.0.23 (nonreserved)SOURCE_SSL_VERIFY_SERVER_CERT; added in 8.0.23 (nonreserved)SOURCE_TLS_CIPHERSUITES; added in 8.0.23 (nonreserved)SOURCE_TLS_VERSION; added in 8.0.23 (nonreserved)SOURCE_USER; added in 8.0.23 (nonreserved)SOURCE_ZSTD_COMPRESSION_LEVEL; added in 8.0.23 (nonreserved)SPATIAL (R)SPECIFIC (R)SQL (R)SQLEXCEPTION (R)SQLSTATE (R)SQLWARNING (R)SQL_AFTER_GTIDSSQL_AFTER_MTS_GAPSSQL_BEFORE_GTIDSSQL_BIG_RESULT (R)SQL_BUFFER_RESULTSQL_CACHE; removed in 8.0.3SQL_CALC_FOUND_ROWS (R)SQL_NO_CACHESQL_SMALL_RESULT (R)SQL_THREADSQL_TSI_DAYSQL_TSI_HOURSQL_TSI_MINUTESQL_TSI_MONTHSQL_TSI_QUARTERSQL_TSI_SECONDSQL_TSI_WEEKSQL_TSI_YEARSRID; added in 8.0.3 (nonreserved)SSL (R)STACKEDSTARTSTARTING (R)STARTSSTATS_AUTO_RECALCSTATS_PERSISTENTSTATS_SAMPLE_PAGESSTATUSSTOPSTORAGESTORED (R)STRAIGHT_JOIN (R)STREAM; added in 8.0.20 (nonreserved)STRINGSUBCLASS_ORIGINSUBJECTSUBPARTITIONSUBPARTITIONSSUPERSUSPENDSWAPSSWITCHESSYSTEM (R); added in 8.0.3 (reserved)TTABLE (R)TABLESTABLESPACETABLE_CHECKSUMTABLE_NAMETEMPORARYTEMPTABLETERMINATED (R)TEXTTHANTHEN (R)THREAD_PRIORITY; added in 8.0.3 (nonreserved)TIES; added in 8.0.2 (nonreserved)TIMETIMESTAMPTIMESTAMPADDTIMESTAMPDIFFTINYBLOB (R)TINYINT (R)TINYTEXT (R)TLS; added in 8.0.21 (nonreserved)TO (R)TRAILING (R)TRANSACTIONTRIGGER (R)TRIGGERSTRUE (R)TRUNCATETYPETYPESUUNBOUNDED; added in 8.0.2 (nonreserved)UNCOMMITTEDUNDEFINEDUNDO (R)UNDOFILEUNDO_BUFFER_SIZEUNICODEUNINSTALLUNION (R)UNIQUE (R)UNKNOWNUNLOCK (R)UNREGISTER; added in 8.0.27 (nonreserved)UNSIGNED (R)UNTILUPDATE (R)UPGRADEUSAGE (R)USE (R)USERUSER_RESOURCESUSE_FRMUSING (R)UTC_DATE (R)UTC_TIME (R)UTC_TIMESTAMP (R)VVALIDATIONVALUEVALUES (R)VARBINARY (R)VARCHAR (R)VARCHARACTER (R)VARIABLESVARYING (R)VCPU; added in 8.0.3 (nonreserved)VIEWVIRTUAL (R)VISIBLEWWAITWARNINGSWEEKWEIGHT_STRINGWHEN (R)WHERE (R)WHILE (R)WINDOW (R); added in 8.0.2 (reserved)WITH (R)WITHOUTWORKWRAPPERWRITE (R)XX509XAXIDXMLXOR (R)YYEARYEAR_MONTH (R)ZZEROFILL (R)ZONE; added in 8.0.22 (nonreserved)MySQL 8.0 新关键字和保留字下面的列表显示了 MySQL 8.0 中添加的关键字和保留字，与 MySQL 5.7 相比。保留关键字用 (R) 标记。A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | R | S | T | U | V | W | ZAACTIVEADMINARRAYATTRIBUTEAUTHENTICATIONBBUCKETSCCHALLENGE_RESPONSECLONECOMPONENTCUME_DIST (R)DDEFINITIONDENSE_RANK (R)DESCRIPTIONEEMPTY (R)ENFORCEDENGINE_ATTRIBUTEEXCEPT (R)EXCLUDEFFACTORFAILED_LOGIN_ATTEMPTSFINISHFIRST_VALUE (R)FOLLOWINGGGEOMCOLLECTIONGET_MASTER_PUBLIC_KEYGET_SOURCE_PUBLIC_KEYGROUPING (R)GROUPS (R)GTID_ONLYHHISTOGRAMHISTORYIINACTIVEINITIALINITIATEINVISIBLEJJSON_TABLE (R)JSON_VALUEKKEYRINGLLAG (R)LAST_VALUE (R)LATERAL (R)LEAD (R)LOCKEDMMASTER_COMPRESSION_ALGORITHMSMASTER_PUBLIC_KEY_PATHMASTER_TLS_CIPHERSUITESMASTER_ZSTD_COMPRESSION_LEVELMEMBERNNESTEDNETWORK_NAMESPACENOWAITNTH_VALUE (R)NTILE (R)NULLSOOF (R)OFFOJOLDOPTIONALORDINALITYORGANIZATIONOTHERSOVER (R)PPASSWORD_LOCK_TIMEPATHPERCENT_RANK (R)PERSISTPERSIST_ONLYPRECEDINGPRIVILEGE_CHECKS_USERPROCESSRRANDOMRANK (R)RECURSIVE (R)REFERENCEREGISTRATIONREPLICAREPLICASREQUIRE_ROW_FORMATRESOURCERESPECTRESTARTRETAINRETURNINGREUSEROLEROW_NUMBER (R)SSECONDARYSECONDARY_ENGINESECONDARY_ENGINE_ATTRIBUTESECONDARY_LOADSECONDARY_UNLOADSKIPSOURCE_AUTO_POSITIONSOURCE_BINDSOURCE_COMPRESSION_ALGORITHMSSOURCE_CONNECT_RETRYSOURCE_DELAYSOURCE_HEARTBEAT_PERIODSOURCE_HOSTSOURCE_LOG_FILESOURCE_LOG_POSSOURCE_PASSWORDSOURCE_PORTSOURCE_PUBLIC_KEY_PATHSOURCE_RETRY_COUNTSOURCE_SSLSOURCE_SSL_CASOURCE_SSL_CAPATHSOURCE_SSL_CERTSOURCE_SSL_CIPHERSOURCE_SSL_CRLSOURCE_SSL_CRLPATHSOURCE_SSL_KEYSOURCE_SSL_VERIFY_SERVER_CERTSOURCE_TLS_CIPHERSUITESSOURCE_TLS_VERSIONSOURCE_USERSOURCE_ZSTD_COMPRESSION_LEVELSRIDSTREAMSYSTEM (R)TTHREAD_PRIORITYTIESTLSUUNBOUNDEDUNREGISTERVVCPUVISIBLEWWINDOW (R)ZZONEMySQL 8.0 删除关键字和保留字以下列表显示了与 MySQL 5.7 相比，在 MySQL 8.0 中删除的关键字和保留字。保留关键字用 (R) 标记。ANALYSEDES_KEY_FILEMASTER_SERVER_IDPARSE_GCOL_EXPRREDOFILESQL_CACHE
-
MySQL表结构自动初始化的create_time与自动更新的update_time 在表结构设计时，经常会设计两个字段：数据创建时间create_time与数据更新时间update_time。通过MySQL的以下机制完成create_time的自动初始化与update_time的自动更新：DEFAULT：默认值机制。CURRENT_TIMESTAMP：MySQL内置函数，用于获取当前时间戳。ON UPDATE：当数据成功更新时。创建表结构时CREATE TABLE `t_temp` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `comment` varchar(10) NOT NULL DEFAULT '' COMMENT '备注', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;修改表结构时ALTER TABLE `t_temp` ADD COLUMN ( `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
