MySQL8.0 GA版本發(fā)布了，展現(xiàn)了眾多新特性，本系列譯文將整理為3篇，本文為第二篇，為大家介紹升級的部分新特性。

***篇：MySQL 8.0的新特性（一）

一、地理信息系統(tǒng) GIS

8.0 版本提供對地形的支持，其中包括了對空間參照系的數(shù)據(jù)源信息的支持，SRS aware spatial數(shù)據(jù)類型，空間索引，空間函數(shù)?？偠灾?，8.0版本可以理解地球表面的經(jīng)緯度信息，而且可以在任意受支持的5000個空間參照系中計算地球上任意兩點之間的距離。

MySQL 8.0 delivers geography support. This includes meta-data support for Spatial Reference System (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions. In short, MySQL 8.0 understands latitude and longitude coordinates on the earth’s surface and can, for example, correctly calculate the distances between two points on the earths surface in any of the about 5000 supported spatial reference systems.

• 空間參照系 Spatial Reference System (SRS)

ST_SPATIAL_REFERENCE_SYSTEMS 存在于information schema視圖庫中，提供了可供使用的SRS坐標系統(tǒng)的名稱。

每個SRS坐標系統(tǒng)都有一個SRID編號。8.0版本支持EPSG Geodetic Parameter Dataseset中的5千多個坐標系統(tǒng)（包括立體模和2D平面地球模型）

The ST_SPATIAL_REFERENCE_SYSTEMS information schema view provides information about available spatial reference systems for spatial data. This view is based on the SQL/MM (ISO/IEC 13249-3) standard. 

Each spatial reference system is identified by an SRID number. MySQL 8.0 ships with about 5000 SRIDs from the EPSG Geodetic Parameter Dataset, covering georeferenced ellipsoids and 2d projections (i.e. all 2D spatial reference systems).

• SRID 地理數(shù)據(jù)類型 SRID aware spatial datatypes

空間類的數(shù)據(jù)類型可以直接從SRS坐標系統(tǒng)的定義中獲取，例如：使用SRID 4326定義進行建表： CREATE TABLE t1 (g GEOMETRY SRID 4326); 。

SRID是適用于地理類型的數(shù)據(jù)類型。只有同一SRID的的數(shù)據(jù)才會被插入到行中。與當前SRID數(shù)據(jù)類型的數(shù)據(jù)嘗試插入時，會報錯。未定義SRID編號的表將可以接受所有SRID編號的數(shù)據(jù)。

Spatial datatypes can be attributed with the spatial reference system definition, for example with SRID 4326 like this: CREATE TABLE t1 (g GEOMETRY SRID 4326); 

The SRID is here a SQL type modifier for the GEOMETRY datatype. Values inserted into a column with an SRID property must be in that SRID. Attempts to insert values with other SRIDs results in an exception condition being raised. Unmodified types, i.e., types with no SRID specification, will continue to accept all SRIDs, as before.

8.0版本增加了 INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS 視圖，可以顯示當前實例中所有地理信息的數(shù)據(jù)行及其對應(yīng)的SRS名稱，編號，地理類型名稱。

MySQL 8.0 adds 

the INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS view as specified in SQL/MM Part 3, Sect. 19.2. This view will list all GEOMETRY columns in the MySQL instance and for each column it will list the standard SRS_NAME , SRS_ID , and GEOMETRY_TYPE_NAME.

• SRID 空間索引 SRID aware spatial indexes

在空間數(shù)據(jù)類型上可以創(chuàng)建空間索引，創(chuàng)建空間索引的列必須非空，例如： CREATE TABLE t1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX(g));

Spatial indexes can be created on spatial datatypes. Columns in spatial indexes must be declared NOT NULL. For example like this: CREATE TABLE t1 (g GEOMETRY SRID 4326 NOT NULL, SPATIAL INDEX(g));

創(chuàng)建空間索引的列必須具有SRID數(shù)據(jù)標識以用于優(yōu)化器使用，如果將空間索引建在沒有SRID數(shù)據(jù)標識的列上，將輸出waring信息。

Columns with a spatial index should have an SRID type modifier to allow the optimizer to use the index. If a spatial index is created on a column that doesn’t have an SRID type modifier, a warning is issued.

• SRID 空間函數(shù) 

8.0 增加了諸如 ST_Distance() 和 ST_Length() 等用于判斷數(shù)據(jù)的參數(shù)是否在SRS中，并計算其空間上的距離。到目前為止，ST_Distance和其他的空間關(guān)系型函數(shù)諸如ST_Within,ST_Intersects,ST_Contains,ST_Crosses都支持地理計算。其運算邏輯與行為參見 SQL/MM Part 3 Spatial

MySQL 8.0 extends spatial functions such as ST_Distance() and ST_Length() to detect that its parameters are in a geographic (ellipsoidal) SRS and to compute the distance on the ellipsoid. So far, ST_Distance and spatial relations such as ST_Within, ST_Intersects, ST_Contains, ST_Crosses, etc. support geographic computations. The behavior of each ST function is as defined in SQL/MM Part 3 Spatial.

二、字符集 Character Sets

8.0版本默認使用UTF8MB4作為默認字符集。相比較5.7版本，SQL性能（諸如排序UTF8MB4字符串）得到了很大的提升。UTF8MB4類型在網(wǎng)頁編碼上正占據(jù)著舉足輕重的地位，將其設(shè)為默認數(shù)據(jù)類型后，將會給絕大多數(shù)的MySQL用戶帶來便利。

MySQL 8.0 makes UTF8MB4 the default character set. SQL performance – such as sorting UTF8MB4 strings – has been improved by a factor of 20 in 8.0 as compared to 5.7. UTF8MB4 is the dominating character encoding for the web, and this move will make life easier for the vast majority of MySQL users.

• 默認的字符集從latin1變?yōu)?nbsp;utf8mb4 ,默認排序校對規(guī)則從 latin1_swedish_ci 變?yōu)閡tf8mb4_800_ci_ai。

The default character set has changed from latin1 to utf8mb4 and the default collation has changed from latin1_swedish_ci to utf8mb4_800_ci_ai.

• utf8mb4同樣也成為libmysql，服務(wù)端命令行工具,server層的默認編碼

The changes in defaults applies to libmysql and server command tools as well as the server itself.

• utf8mb4同樣也成為MySQL測試框架的默認編碼

The changes are also reflected in MTR tests, running with new default charset.

• 排序校對規(guī)則的權(quán)重與大小寫基于Unicode委員會16年公布的Unicode 9.0.0版本。

The collation weight and case mapping are based on Unicode 9.0.0 , announced by the Unicode committee on Jun 21, 2016.

• 在以往的MySQL版本中，latin1編碼中的21種語言的特殊大小寫和排序校對規(guī)則被引入了 utf8mb4 排序校對規(guī)則。例如：捷克語的排序校對規(guī)則變成了utf8mb4_cs_800_ai_ci。

The 21 language specific case insensitive collations available for latin1 (MySQL legacy) have been implemented forutf8mb4 collations, for example the Czech collation becomes utf8mb4_cs_800_ai_ci. See complete list in WL#9108 . See blog post by Xing Zhang here .

• 增加了對特殊語境和重音敏感的排序校對規(guī)則的支持。8.0版本支持 DUCET (Default Unicode Collation Entry Table)全部三級排序校對規(guī)則。

Added support for case and accent sensitive collations. MySQL 8.0 supports all 3 levels of collation weight defined by DUCET (Default Unicode Collation Entry Table). See blog post by Xing Zhang here.

• utf8mb4 的 utf8mb4_ja_0900_as_cs 排序校驗規(guī)則對日語字符支持三級權(quán)重的排序。

Japanese utf8mb4_ja_0900_as_cs collation for utf8mb4 which sorts characters by using three levels’ weight. This gives the correct sorting order for Japanese. See blog post by Xing Zhang here.

• 對日語有額外的假名支持特性， utf8mb4_ja_0900_as_cs_ks中的ks表示假名區(qū)分。

Japanese with additional kana sensitive feature, utf8mb4_ja_0900_as_cs_ks, where ‘ks’ stands for ‘kana sensitive’. See blog post by Xing Zhang here.

• 把 Unicode 9.0.0之前所有排序校驗規(guī)則中的不填補變成填補字符，此舉有利于提升字符串的一致性和性能。例如把字符串末尾的空格按照其他字符對待。之前的排序校驗規(guī)則在處理這種情況時保留字符串原樣。

Changed all new collations, from Unicode 9.0.0 forward, to be NO PAD instead of PAD STRING, ie., treat spaces at the end of a string like any other character. This is done to improve consistency and performance. Older collations are left in place.

See also blog posts by Bernt Marius Johnsen here, here and here.

 

三、數(shù)據(jù)類型 Datatypes

• 二進制數(shù)據(jù)類型的Bit-wise操作

8.0版本擴展了 bit-wise操作（如bit-wise AND等）的使用范圍，使得其在所有 BINARY 數(shù)據(jù)類型上都適用。在此之前只支持整型數(shù)據(jù)，若強行在二進制數(shù)據(jù)類型上使用Bit-wise操作，將會隱式轉(zhuǎn)換為64位的BITINT類型，并可能丟失若干位的數(shù)據(jù)。

從8.0版本之后，bit-wise操作可以在 BINARY 和BLOB類型上使用，且不用擔心精確度下降的問題。

MySQL 8.0 extends the bit-wise operations (‘bit-wise AND’, etc) to also work with [VAR]BINARY/[TINY|MEDIUM|LONG]BLOB. Prior to 8.0 bit-wise operations were only supported for integers. If you used bit-wise operations on binaries the arguments were implicitly cast to BIGINT (64 bit) before the operation, thus possibly losing bits. 

From 8.0 and onward bit-wise operations work for all BINARY and BLOB data types, casting arguments such that bits are not lost.

• IPV6操作

8.0版本通過支持 BINARY 上的Bit-wise操作提升了IPv6數(shù)據(jù)的可操作性。5.6版本中引入了支持IPv6地址和16位二進制數(shù)據(jù)的互相轉(zhuǎn)換的INET6_ATON() 和 INET6_NTOA() 函數(shù)。

但是直到8.0之前，由于上一段中的問題我們都無法講IPv6轉(zhuǎn)換函數(shù)和bit-wise操作結(jié)合起來。由于 INET6_ATON() 可以正確的返回128bit的VARBINARY(16)，如果我們想要將一個IPv6地址與網(wǎng)關(guān)地址進行比對，現(xiàn)在就可以使用 INET6_ATON(address)& INET6_ATON(network) 操作。

MySQL 8.0 improves the usability of IPv6 manipulation supporting bit-wise operations on BINARY data types. In MySQL 5.6 we introduced the INET6_ATON() and INET6_NTOA() functions which convert IPv6 addresses between text form like 'fe80::226:b9ff:fe77:eb17' and VARBINARY(16). 

However, until now we could not combine these IPv6 functions with bit-wise operations since such operations would – wrongly – convert output to BIGINT. For example, if we have an IPv6 address and want to test it against a network mask, we can now use INET6_ATON(address)& INET6_ATON(network) because INET6_ATON() correctly returns the VARBINARY(16)datatype (128 bits). See blog post by Catalin Besleaga here.

• UUID 操作

8.0版本通過增加了三個新的函數(shù)（UUID_TO_BIN(), BIN_TO_UUID(), 和 IS_UUID()）提升了UUID的可用性。UUID_TO_BIN()可以將UUID格式的文本轉(zhuǎn)換成VARBINARY(16), BIN_TO_UUID()則與之相反， IS_UUID()用來校驗UUID的有效性。將UUID以 VARBINARY(16) 的方式存儲后，就可以使用實用的索引了。

 UUID_TO_BIN() 函數(shù)可以原本轉(zhuǎn)換后的二進制數(shù)值中的時間相關(guān)位（UUID生成時有時間關(guān)聯(lián)）移到數(shù)據(jù)的開頭，這樣對索引來說更加友好而且可以減少在B樹中的隨機插入，從而減少了插入耗時。

MySQL 8.0 improves the usability of UUID manipulations by implementing three new SQL functions: UUID_TO_BIN(), BIN_TO_UUID(), and IS_UUID(). The first one converts from UUID formatted text to VARBINARY(16), the second one from VARBINARY(16) to UUID formatted text, and the last one checks the validity of an UUID formatted text. The UUID stored as a VARBINARY(16) can be indexed using functional indexes. 

The functions UUID_TO_BIN() and UUID_TO_BIN() can also shuffle the time-related bits and move them at the beginning making it index friendly and avoiding the random inserts in the B-tree, this way reducing the insert time. The lack of such functionality has been mentioned as one of the drawbacks of using UUID’s. See blog post by Catalin Besleaga here.

四、消耗敏感的模型 

• 查詢優(yōu)化器將會照顧到數(shù)據(jù)緩沖的狀況

8.0版本自動地根據(jù)數(shù)據(jù)是否存在于內(nèi)存中而選擇查詢計劃，在以往的版本中，消耗敏感的模型始終假設(shè)數(shù)據(jù)在磁盤上。

正因為現(xiàn)在查詢內(nèi)存數(shù)據(jù)和查詢硬盤數(shù)據(jù)的消耗常數(shù)不同，因此優(yōu)化器會根據(jù)數(shù)據(jù)的位置選擇更加優(yōu)化的讀取數(shù)據(jù)方式。

MySQL 8.0 chooses query plans based on knowledge about whether data resides in-memory or on-disk. This happens automatically, as seen from the end user there is no configuration involved. Historically, the MySQL cost model has assumed data to reside on spinning disks. 

The cost constants associated with looking up data in-memory and on-disk are now different, thus, the optimizer will choose more optimal access methods for the two cases, based on knowledge of the location of data. See blog post by Øystein Grøvlen here.

• 查詢優(yōu)化器的直方圖 

8.0版本加入了直方圖統(tǒng)計數(shù)據(jù)。用戶可以根據(jù)直方圖針對表中的某列（一般為非索引列）生成數(shù)據(jù)分布統(tǒng)計信息，這樣優(yōu)化器就可以利用這些信息去尋覓更加優(yōu)化的查詢計劃。

直方圖最常見的使用場景就是計算字段的選擇性。

MySQL 8.0 implements histogram statistics. With Histograms, the user can create statistics on the data distribution for a column in a table, typically done for non-indexed columns, which then will be used by the query optimizer in finding the optimal query plan. 

The primary use case for histogram statistics is for calculating the selectivity (filter effect) of predicates of the form “COLUMN operator CONSTANT”.

用以創(chuàng)建直方圖的 ANALYZE TABLE 語法現(xiàn)已被擴展了兩個新子句： UPDATE HISTOGRAM ON column [, column] [WITH n BUCKETS]和DROP HISTOGRAM ON column [, column]。

直方圖的總計總數(shù)（桶）是可以選的，默認100。直方圖的統(tǒng)計信息被存儲在詞典表column_statistics中，并可以使用

information_schema.COLUMN_STATISTICS進行查看。由于JSON數(shù)據(jù)格式的靈活性，直方圖現(xiàn)在以JSON對象存儲。

根據(jù)表的大小，ANALYZE TABLE命令會自動的判斷是否要表進行采樣，甚至會根據(jù)表中數(shù)據(jù)的分布情況和統(tǒng)計總量來決定創(chuàng)建等頻或者等高的直方圖。

The user creates a histogram by means of the ANALYZE TABLE syntax which has been extended to accept two new clauses: UPDATE HISTOGRAM ON column [, column] [WITH n BUCKETS] and DROP HISTOGRAM ON column [, column]. 

The number of buckets is optional, the default is 100. The histogram statistics are stored in the dictionary table “column_statistics” and accessible through the view information_schema.COLUMN_STATISTICS. The histogram is stored as a JSON object due to the flexibility of the JSON datatype. 

ANALYZE TABLE will automatically decide whether to sample the base table or not, based on table size. It will also decide whether to build a singleton or a equi-height histogram based on the data distribution and the number of buckets specified. See blog post by Erik Frøseth here.

五、正則表達式 

與UTF8MB4的正則支持一同，8.0版本也增加了諸如 REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), 和REGEXP_SUBSTR()等新函數(shù)。

另外，系統(tǒng)中還增加了用以控制正則表達式致性的 regexp_stack_limit (默認8000000比特) 和 regexp_time_limit (默認32步) 參數(shù)。REGEXP_REPLACE()也是社區(qū)中受呼聲比較高的特性。

MySQL 8.0 supports regular expressions for UTF8MB4 as well as new functions like REGEXP_INSTR(), REGEXP_LIKE(), REGEXP_REPLACE(), and REGEXP_SUBSTR(). 

The system variables regexp_stack_limit (default 8000000 bytes) and regexp_time_limit (default 32 steps) have been added to control the execution. The REGEXP_REPLACE() function is one of the most requested features by the MySQL community, for example see feature request reported as BUG #27389 by Hans Ginzel. See also blog posts by Martin Hansson here and Bernt Marius Johnsen here.

六、運維自動化特性

開發(fā)向的運維關(guān)心數(shù)據(jù)庫實例的可操作型，通常即可靠性，可用性，性能，安全，可觀測性，可管理性。關(guān)于InnoDB Cluster和MGR的可靠性我們將會另起新篇單獨介紹，接下來的段落將會介紹關(guān)于8.0版本針對表在其他可操作性上的改變。

Dev Ops care about operational aspects of the database, typically about reliability, availability, performance, security, observability, and manageability. High Availability comes with MySQL InnoDB Cluster and MySQL Group Replication which will be covered by a separate blog post. Here follows what 8.0 brings to the table in the other categories.

七、可靠性

8.0版本在整體上 增加了可靠性，原因如下：

MySQL 8.0 increases the overall reliability of MySQL because :

1、8.0版本將元信息存儲與久經(jīng)考驗的事務(wù)性存儲引擎InnoDB中。諸如用戶權(quán)限表，數(shù)據(jù)字典表，現(xiàn)在都使用 InnoDB進行存儲。

MySQL 8.0 stores its meta-data into InnoDB, a proven transactional storage engine. System tables such as Users and Privileges as well as Data Dictionary tables now reside in InnoDB.

2、8.0版本消除了會導(dǎo)致非一致性的一處隱患。在5.7及以前的版本中，存在著服務(wù)層和引擎層兩份數(shù)據(jù)字典，因而可能導(dǎo)致在故障情況下的數(shù)據(jù)字典間的同步失敗。在8.0版本中，只有一份數(shù)據(jù)字典。

MySQL 8.0 eliminates one source of potential inconsistency. In 5.7 and earlier versions there are essentially two data dictionaries, one for the Server layer and one for the InnoDB layer, and these can get out of sync in some crashing scenarios. In 8.0 there is only one data dictionary.

3、8.0版本實現(xiàn)了原子化，無懼宕機的DDL。根據(jù)這個特性，DDL語句要么被全部執(zhí)行，要么全部未執(zhí)行。對于復(fù)制環(huán)境來說這是至關(guān)重要的，否則會導(dǎo)致主從之間因為表結(jié)構(gòu)不一致，數(shù)據(jù)漂移的情況。

MySQL 8.0 ensures atomic, crash safe DDL. With this the user is guaranteed that any DDL statement will either be executed fully or not at all. This is particularly important in a replicated environment, otherwise there can be scenarios where masters and slaves (nodes) get out of sync, causing data-drift.

基于新的事務(wù)型數(shù)據(jù)字典，可靠性得到了提高。

This work is done in the context of the new, transactional data dictionary. See blog posts by Staale Deraas here and here.