Нови възможности за разработчици в MySQL 8.0.13

Ах, каква седмица вече и още не е приключила! Първо, Oracle пуснаха Oracle XE 18c (както писах в Неделя), а вчера (22-ри Октомври) пристигна MySQL 8.0.13 заедно (както вече обичайно) с целия флот (Router, Shell, Workbench и C++/J/ODBC/NET/Python/Node.js Connectors). Бях нетърпелив да прочета журнала с промените и както посочената статия от блога на MySQL Server също подсказва има някои интересни нови възможности за дизайнери и разработчици. Тук отдолу е моя избор и няколко думи за надграждане от 8.0.12.

Стойностите по подразбиране в SQL вече могат да са също функция или израз

Вече е възможно да има стойности по подразбиране като функция или израз. Това е важна нова възможност. Преди само буквални стойности бяха позволени, което беше доста ограничаващо. Премахването на това ограничение означава, че вече е възможно:

• да се създават UUID стойности по подразбиране (с използването на DEFAULT (uuid_to_bin(uuid())) например);
• да се създават стойности по подразбиране за геометрични типове данни (с използването на DEFAULT (POINT(0,0)) или DEFAULT (ST_PointFromText('POINT(42.69751 23.32415)', 4326)) например);
• да се създават стойности по подразбиране за JSON колони (с използването на DEFAULT (JSON_ARRAY()) или DEFAULT ('[]') например); и
• други сложни стойности (като DEFAULT (CURRENT_DATE + INTERVAL 1 DAY) or DEFAULT (PI() * POW(r, 2)) например) с използването на функция или функции в израз.

Забележка: Моля, обърнете внимание на скобите обграждащи израза в DEFAULT (виж Handling of Explicit Defaults as of MySQL 8.0.13 в наръчника). MySQL Workbench 8.0.13 отново има някои проблеми с синтактичния разбор на новия синтаксис (виж екранната снима отдолу), така че отворих бъг 92900 и той вече беше потвърден:

Пробвах новата функционалност със следното:

CREATE TABLE def_expr (
  id        INT           NOT NULL AUTO_INCREMENT,
  uuid_def  BINARY(16)    DEFAULT (uuid_to_bin(uuid())),
  geo_def   POINT         DEFAULT (Point(0,0)),
  geo_def2  GEOMETRY      DEFAULT (ST_PointFromText('POINT(42.69751 23.32415)', 4326)),
  json_def  JSON          DEFAULT (JSON_ARRAY()),
  json_def2 JSON          DEFAULT ('[]') /* this works too */,
  tomorrow  DATE          DEFAULT (CURDATE() + INTERVAL 1 DAY),
  radius    INT           DEFAULT (FLOOR(1 + (RAND() * 10))),
  area      DECIMAL(10,3) DEFAULT (ROUND(PI() * POW(radius, 2), 3)),

  PRIMARY KEY (id),
  UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE
);

След това вмъкнах две пробни линии и получих следните резултати:

INSERT INTO def_expr VALUES (); /* x 2 */

SELECT id,
       bin_to_uuid(uuid_def) uuid_def,
       ST_AsText(geo_def)    geo_def,
       ST_AsText(geo_def2)   geo_def2,
       json_def, json_def2,
       tomorrow, radius, area
  FROM def_expr;

+----+--------------------------------------+------------+--------------------------+----------+-----------+------------+--------+---------+
| id | uuid_def                             | geo_def    | geo_def2                 | json_def | json_def2 | tomorrow   | radius | area    |
+----+--------------------------------------+------------+--------------------------+----------+-----------+------------+--------+---------+
|  1 | a2747ee0-d6ee-11e8-b02e-02004c4f4f50 | POINT(0 0) | POINT(42.69751 23.32415) | []       | []        | 2018-10-24 |      2 |  12.566 |
|  2 | a2ff920b-d6ee-11e8-b02e-02004c4f4f50 | POINT(0 0) | POINT(42.69751 23.32415) | []       | []        | 2018-10-24 |     10 | 314.159 |
+----+--------------------------------------+------------+--------------------------+----------+-----------+------------+--------+---------+
2 rows in set (0.0021 sec)

Яко, нали? Нетърпелив съм да го пробвам с проектите ми.

Функционални SQL индекси

Вече можете да имате функционални индекси (виж Functional Key Parts в наръчника), което означава, че индекса се изгражда от стойности на изрази, вместо стойности на колони или началната част на стойности на колони. С други думи можете да индексирате стойности, които не са съхранени в таблица, което смятам, че може да бъде доста мощно в определени случаи. В съставни индекси с няколко части на ключа можете да смесвате функционални и нефункционални (обикновени) части. Функционалните части на ключа са реализирани, чрез скрити виртуални породени колони (виж CREATE TABLE and Generated Columns в наръчника), които са налични от MySQL 5.7.6 (от 2015-03-09). Това също така значи, че функционалните части на ключа имат същите ограничения (т.е. детерминирани вградени функции и оператори са разрешени, но подзаявки, параметри, променливи, съхранени и потребителски функци не са разрешени).

За да пробвам новата функционалност се сетих, че IP адреси обичайно се съхраняват като текст в базите и после се четат и разбиват, но адреса може да се представи като целочислена стойност (без знак), което изисква само 4 байта. Така, че използвах следното:

CREATE TABLE func_index (
  id        INT        NOT NULL AUTO_INCREMENT,
  ipaddr4   INT UNSIGNED /* 4 bytes */,

  PRIMARY KEY (id),
  UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE,
  INDEX func_idx ((INET_NTOA(ipaddr4)))
);

Забележка: Моля, обърнете внимание на скобите обграждащи израза. Без тях ще има код 1064 (синтактична грешка). Очаквано MySQL Workbench 8.0.13 не е готов за новия синтаксис, така че отворих бъг 92908 и той също вече беше потвърден.

След това вмъкнах една линия и пуснах обяснение на плана за изпълнение на двете SELECT заявки отдолу:

INSERT INTO func_index (ipaddr4) VALUES (INET_ATON('192.168.1.1'));

SELECT * FROM func_index WHERE ipaddr4 = INET_ATON('192.168.1.1');

SELECT * FROM func_index WHERE INET_NTOA(ipaddr4) = '192.168.1.1';

Първата ще доведе до пълно сканиране на таблицата, докато втората ще доведе до четене на функционалния индекс func_idx. За да може заявката да използва индекса е важно израза в WHERE клаузата да съвпада с този в дефиницията на индекса.

Възможност за забрана на създаването на таблици без първичен ключ

Вече има системна променлива sql_require_primary_key, която предотвратява създаването или промяната на таблици без първичен ключ ако е зададена. В този случай CREATE или ALTER зявки вече ще пропадат с код за грешка 3750 (виж също SQL състояние ER_TABLE_WITHOUT_PK).

Пробвах тази нова възможност задавайки глобалната променлива в сесия така:

SET GLOBAL sql_require_primary_key = ON;

но успях да създам таблица без първичен ключ. Трябва да проверя отново утре и евентуално да докладвам бъг. Задаването на променливата в my.ini и след рестартиране на сървъра работи както трябва:

SQL> CREATE TABLE tab_no_pk (test INT);
ERROR: 3750: Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.

Вече ще си почина от разработчици незапознати с релационната теория :-)

Геометрична трансформация

Вече е възможно да се преобразува геометрия от една пространствена референтна система (SRS) към друга с вградената функция ST_Transform, която приема като втори аргумент целевата SRS. Нямам голям интерес към GIS, така че не съм я пробвал, но изглеждаше, че си струва да бъде спомената.

Няколко думи за надграждането (от 8.0.12)

Надграждането на Windows инсталацията ми беше безпроблемно с MySQL Installer за Windows. Обаче, имах проблем с Linux инсталацията ми, която съдържа повече бази данни. Сървъра не можеше да стартира и в журнала с грешки намерих грешки като следната:

2018-10-23T12:47:49.627732Z 1 [ERROR] [MY-013235] [Server] Error in parsing View 'db'.'aView' during upgrade. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc' at line 1

Отворих бъг 92898 за това и проблема вече беше обяснен от поддръжката с несъвместима промяна (виж SQL Syntax Notes от бележките към версията). Почти сигурен съм, че четох някъде за това, че ASC или DESC ключовите думи за GROUP BY клаузата са обявени за архаични и наистина не помня защо съм използвал такъв синтаксис, но ме свари неподготвен, така че се наложи да надградя заявката на изгледите.

Има доста повече в MySQL 8.0.13 и горещо ви препоръчвам да прегледате статията The MySQL 8.0.13 Maintenance Release is Generally Available и бележките към версията, след което да изследвате сами.