搜索到
15
篇与
MySQL
的结果
-
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，这个名字许多人大概熟悉。如今 -
实用！一键生成数据库文档 数据库文档图一、数据库支持[x] MySQL[x] MariaDB[x] TIDB[x] Oracle[x] SqlServer[x] PostgreSQL[x] Cache DB二、配置1、pom文件引入screw核心包，HikariCP数据库连接池，HikariCP号称性能最出色的数据库连接池。 <!-- screw核心 --> <dependency> <groupId>cn.smallbun.screw</groupId> <artifactId>screw-core</artifactId> <version>1.0.3</version> </dependency> <!-- HikariCP --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.4.5</version> </dependency> <!--mysql driver--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency>2、配置数据源配置数据源，设置 useInformationSchema 可以获取tables注释信息。spring.datasource.url=jdbc:mysql://45.93.1.5:3306/fire?useUnicode=true&characterEncoding=UTF-8&useSSL=false spring.datasource.username=root spring.datasource.password=123456 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.xa.properties.useInformationSchema=true3、screw 核心配置screw有两种执行方式，第一种是pom文件配置，另一种是代码执行。 <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> <plugin> <groupId>cn.smallbun.screw</groupId> <artifactId>screw-maven-plugin</artifactId> <version>1.0.3</version> <dependencies> <!-- HikariCP --> <dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.4.5</version> </dependency> <!--mysql driver--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.20</version> </dependency> </dependencies> <configuration> <!--username--> <username>root</username> <!--password--> <password>123456</password> <!--driver--> <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName> <!--jdbc url--> <jdbcUrl>jdbc:mysql://41.92.6.5:3306/fire</jdbcUrl> <!--生成文件类型--> <fileType>HTML</fileType> <!--打开文件输出目录--> <openOutputDir>false</openOutputDir> <!--生成模板--> <produceType>freemarker</produceType> <!--文档名称 为空时:将采用[数据库名称-描述-版本号]作为文档名称--> <!--<docName>测试文档名称</docName>--> <!--描述--> <description>数据库文档生成</description> <!--版本--> <version>${project.version}</version> <!--标题--> <title>fire数据库文档</title> </configuration> <executions> <execution> <phase>compile</phase> <goals> <goal>run</goal> </goals> </execution> </executions> </plugin> </plugins> </build>配置完以后在 maven project->screw 双击执行ok。在这里插入图片描述代码生成方式也非常简单。@SpringBootTest public class ScrewApplicationTests { @Autowired ApplicationContext applicationContext; @Test void contextLoads() { DataSource dataSourceMysql = applicationContext.getBean(DataSource.class); // 生成文件配置 EngineConfig engineConfig = EngineConfig.builder() // 生成文件路径，自己mac本地的地址，这里需要自己更换下路径 .fileOutputDir("D:/") // 打开目录 .openOutputDir(false) // 文件类型 .fileType(EngineFileType.HTML) // 生成模板实现 .produceType(EngineTemplateType.freemarker).build(); // 生成文档配置（包含以下自定义版本号、描述等配置连接） Configuration config = Configuration.builder() .version("1.0.3") .description("生成文档信息描述") .dataSource(dataSourceMysql) .engineConfig(engineConfig) .produceConfig(getProcessConfig()) .build(); // 执行生成 new DocumentationExecute(config).execute(); } /** * 配置想要生成的表+ 配置想要忽略的表 * * @return 生成表配置 */ public static ProcessConfig getProcessConfig() { // 忽略表名 List<String> ignoreTableName = Arrays.asList("a", "test_group"); // 忽略表前缀，如忽略a开头的数据库表 List<String> ignorePrefix = Arrays.asList("a", "t"); // 忽略表后缀 List<String> ignoreSuffix = Arrays.asList("_test", "czb_"); return ProcessConfig.builder() //根据名称指定表生成 .designatedTableName(Arrays.asList("fire_user")) //根据表前缀生成 .designatedTablePrefix(new ArrayList<>()) //根据表后缀生成 .designatedTableSuffix(new ArrayList<>()) //忽略表名 .ignoreTableName(ignoreTableName) //忽略表前缀 .ignoreTablePrefix(ignorePrefix) //忽略表后缀 .ignoreTableSuffix(ignoreSuffix).build(); } }4、文档格式screw 有 HTML、DOC、MD 三种格式的文档。代码中的修改.fileType(EngineFileType.HTML)或者pom文件<fileType>MD</fileType>DOC文档样式work文档HTML文档样式在这里插入图片描述MD文档样式在这里插入图片描述
-
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 );
-
MySQL · 性能优化 · MySQL常见SQL错误用法 简介： 前言 MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上，甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢，CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下，供大家参考。前言MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上，甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇到一些比如响应时间慢，CPU打满等情况。阿里云RDS专家服务团队帮助云上客户解决过很多紧急问题。现将《ApsaraDB专家诊断报告》中出现的部分常见SQL问题总结如下，供大家参考。常见SQL错误用法1. LIMIT 语句分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。比如对于下面简单的语句，一般DBA想到的办法是在type, name, create_time字段上加组合索引。这样条件排序都能有效的利用到索引，性能迅速提升。SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' ORDER BY create_time LIMIT 1000, 10; 好吧，可能90%以上的DBA解决该问题就到此为止。但当 LIMIT 子句变成 “LIMIT 1000000,10” 时，程序员仍然会抱怨：我只取10条记录为什么还是慢？要知道数据库也并不知道第1000000条记录从什么地方开始，即使有索引也需要从头计算一次。出现这种性能问题，多数情形下是程序员偷懒了。在前端数据浏览翻页，或者大数据分批导出等场景下，是可以将上一页的最大值当成参数作为查询条件的。SQL重新设计如下：SELECT * FROM operation WHERE type = 'SQLStats' AND name = 'SlowLog' AND create_time > '2017-03-16 14:00:00' ORDER BY create_time limit 10;在新设计下查询时间基本固定，不会随着数据量的增长而发生变化。2. 隐式转换SQL语句中查询变量和字段定义类型不匹配是另一个常见的错误。比如下面的语句：mysql> explain extended SELECT * > FROM my_balance b > WHERE b.bpn = 14000000123 > AND b.isverified IS NULL ; mysql> show warnings; | Warning | 1739 | Cannot use ref access on index 'bpn' due to type or collation conversion on field 'bpn'其中字段bpn的定义为varchar(20)，MySQL的策略是将字符串转换为数字之后再比较。函数作用于表字段，索引失效。上述情况可能是应用程序框架自动填入的参数，而不是程序员的原意。现在应用框架很多很繁杂，使用方便的同时也小心它可能给自己挖坑。3. 关联更新、删除虽然MySQL5.6引入了物化特性，但需要特别注意它目前仅仅针对查询语句的优化。对于更新或删除需要手工重写成JOIN。比如下面UPDATE语句，MySQL实际执行的是循环/嵌套子查询（DEPENDENT SUBQUERY)，其执行时间可想而知。UPDATE operation o SET status = 'applying' WHERE o.id IN (SELECT id FROM (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ( 'done' ) ORDER BY o.parent, o.id LIMIT 1) t); 执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARYoindex PRIMARY8 24Using where; Using temporary2DEPENDENT SUBQUERY Impossible WHERE noticed after reading const tables3DERIVEDorefidx_2,idx_5idx_58const1Using where; Using filesort重写为JOIN之后，子查询的选择模式从DEPENDENT SUBQUERY变成DERIVED,执行速度大大加快，从7秒降低到2毫秒。UPDATE operation o JOIN (SELECT o.id, o.status FROM operation o WHERE o.group = 123 AND o.status NOT IN ( 'done' ) ORDER BY o.parent, o.id LIMIT 1) t ON o.id = t.id SET status = 'applying' 执行计划简化为：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY Impossible WHERE noticed after reading const tables2DERIVEDorefidx_2,idx_5idx_58const1Using where; Using filesort4. 混合排序MySQL不能利用索引进行混合排序。但在某些场景，还是有机会使用特殊方法提升性能的。SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id ORDER BY a.is_reply ASC, a.appraise_time DESC LIMIT 0, 20 执行计划显示为全表扫描：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEaALLidx_orderidNULLNULLNULL1967647Using filesort1SIMPLEoeq_refPRIMARYPRIMARY122a.orderid1NULL由于is_reply只有0和1两种状态，我们按照下面的方法重写后，执行时间从1.58秒降低到2毫秒。SELECT * FROM ((SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0 ORDER BY appraise_time DESC LIMIT 0, 20) UNION ALL (SELECT * FROM my_order o INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 1 ORDER BY appraise_time DESC LIMIT 0, 20)) t ORDER BY is_reply ASC, appraisetime DESC LIMIT 20; 5. EXISTS语句MySQL对待EXISTS子句时，仍然采用嵌套子查询的执行方式。如下面的SQL语句：SELECT * FROM my_neighbor n LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx' WHERE n.topic_status < 4 AND EXISTS(SELECT 1 FROM message_info m WHERE n.id = m.neighbor_id AND m.inuser = 'xxx') AND n.topic_type <> 5 执行计划为：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARYnALL NULLNULLNULL1086041Using where1PRIMARYsraref idx_user_id123const1Using where2DEPENDENT SUBQUERYmref idx_message_info122const1Using index condition; Using where去掉exists更改为join，能够避免嵌套子查询，将执行时间从1.93秒降低为1毫秒。SELECT * FROM my_neighbor n INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx' LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx' WHERE n.topic_status < 4 AND n.topic_type <> 5 新的执行计划：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEmref idx_message_info122const1Using index condition1SIMPLEneq_ref PRIMARY122ighbor_id1Using where1SIMPLEsraref idx_user_id123const1Using where6. 条件下推外部查询条件不能够下推到复杂的视图或子查询的情况有：聚合子查询；含有LIMIT的子查询；UNION 或UNION ALL子查询；输出字段中的子查询；如下面的语句，从执行计划可以看出其条件作用于聚合子查询之后：SELECT * FROM (SELECT target, Count(*) FROM operation GROUP BY target) t WHERE target = 'rm-xxxx' idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>ref<auto_key0><auto_key0>514const2Using where2DERIVEDoperationindexidx_4idx_4519NULL20Using index确定从语义上查询条件可以直接下推后，重写如下：SELECT target, Count(*) FROM operation WHERE target = 'rm-xxxx' GROUP BY target执行计划变为：idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEoperationrefidx_4idx_4514const1Using where; Using index关于MySQL外部条件不能下推的详细解释说明请参考以前文章： MySQL · 性能优化 · 条件下推到物化表7. 提前缩小范围先上初始SQL语句：SELECT * FROM my_order o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid WHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 15 该SQL语句原意是：先做一系列的左连接，然后排序取前15条记录。从执行计划也可以看出，最后一步估算排序记录数为90万，时间消耗为12秒。idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEoALLNULLNULLNULLNULL909119Using where; Using temporary; Using filesort1SIMPLEueq_refPRIMARYPRIMARY4o.uid1NULL1SIMPLEpALLPRIMARYNULLNULLNULL6Using where; Using join buffer (Block Nested Loop)由于最后WHERE条件以及排序均针对最左主表，因此可以先对my_order排序提前缩小数据量再做左连接。SQL重写后如下，执行时间缩小为1毫秒左右。SELECT * FROM ( SELECT * FROM my_order o WHERE ( o.display = 0 ) AND ( o.ostaus = 1 ) ORDER BY o.selltime DESC LIMIT 0, 15 ) o LEFT JOIN my_userinfo u ON o.uid = u.uid LEFT JOIN my_productinfo p ON o.pid = p.pid ORDER BY o.selltime DESC limit 0, 15再检查执行计划：子查询物化后（select_type=DERIVED)参与JOIN。虽然估算行扫描仍然为90万，但是利用了索引以及LIMIT 子句后，实际执行时间变得很小。idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1PRIMARY<derived2>ALLNULLNULLNULLNULL15Using temporary; Using filesort1PRIMARYueq_refPRIMARYPRIMARY4o.uid1NULL1PRIMARYpALLPRIMARYNULLNULLNULL6Using where; Using join buffer (Block Nested Loop)2DERIVEDoindexNULLidx_15NULL909112Using where8. 中间结果集下推再来看下面这个已经初步优化过的例子(左连接中的主表优先作用查询条件)：SELECT a.*, c.allocated FROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) a LEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources GROUP BY resourcesid) c ON a.resourceid = c.resourcesid那么该语句还存在其它问题吗？不难看出子查询 c 是全表聚合查询，在表数量特别大的情况下会导致整个语句的性能下降。其实对于子查询 c，左连接最后结果集只关心能和主表resourceid能匹配的数据。因此我们可以重写语句如下，执行时间从原来的2秒下降到2毫秒。SELECT a.*, c.allocated FROM ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) a LEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) c ON a.resourceid = c.resourcesid但是子查询 a 在我们的SQL语句中出现了多次。这种写法不仅存在额外的开销，还使得整个语句显的繁杂。使用WITH语句再次重写：WITH a AS ( SELECT resourceid FROM my_distribute d WHERE isdelete = 0 AND cusmanagercode = '1234567' ORDER BY salecode limit 20) SELECT a.*, c.allocated FROM a LEFT JOIN ( SELECT resourcesid， sum(ifnull(allocation, 0) * 12345) allocated FROM my_resources r, a WHERE r.resourcesid = a.resourcesid GROUP BY resourcesid) c ON a.resourceid = c.resourcesidAliSQL即将推出WITH语法，敬请期待。总结数据库编译器产生执行计划，决定着SQL的实际执行方式。但是编译器只是尽力服务，所有数据库的编译器都不是尽善尽美的。上述提到的多数场景，在其它数据库中也存在性能问题。了解数据库编译器的特性，才能避规其短处，写出高性能的SQL语句。程序员在设计数据模型以及编写SQL语句时，要把算法的思想或意识带进来。编写复杂SQL语句要养成使用WITH语句的习惯。简洁且思路清晰的SQL语句也能减小数据库的负担
