그 외

1. Data type

데이터 타입의 기본값으로 표현식 사용 지원

• 이전에 기본값을 할당할 수 없었던 BLOB, TEXT, GEOMETRY, JSON 데이터 타입의 기본값으로 표현식을 사용하는 것 포함

세부 정보

CREATE TABLE t1 (
  -- literal defaults
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- expression defaults
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
  p POINT       DEFAULT (Point(0,0)),
  j JSON        DEFAULT (JSON_ARRAY())
);

2. Common table expressions

재귀/비재귀 모두 WITH Common table expressions (CTE) 지원

• SELECT 문 및 기타 특정 문 앞에 있는 WITH 절을 허용하여 구현된 명명된 임시 결과 집합을 사용 가능

• 재귀 CTE의 재귀 SELECT 부분이 LIMIT 절을 지원

세부 정보

WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte LIMIT 10
)
SELECT * FROM cte;

/*
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
*/

3. Window functions

윈도우 함수 지원

• 쿼리 행 집합에 대해 집계와 유사한 작업을 수행하지만 각 쿼리 행에 대한 결과를 생성

• OW_NUMBER(), RANK(), LAG(), LEAD() 등

• 여러 기존 집계 함수를 윈도우 함수(예: SUM(),AVG())로 사용 가능

세부 정보

SELECT
  val,
  ROW_NUMBER() OVER w AS 'row_number',
  RANK() OVER w AS 'rank',
  LAG(val) OVER w AS 'lag',
  LEAD(val) OVER w AS 'lead'
FROM numbers
WINDOW w AS (ORDER BY val);

/*
+------+------------+------+------+------+
| val  | row_number | rank | lag  | lead |
+------+------------+------+------+------+
|    1 |          1 |    1 | NULL |    2 |
|    2 |          2 |    2 |    1 |    2 |
|    2 |          3 |    2 |    2 |    3 |
|    3 |          4 |    4 |    2 |    3 |
|    3 |          5 |    4 |    3 |    3 |
|    3 |          6 |    4 |    3 |    4 |
|    4 |          7 |    7 |    3 |    4 |
|    4 |          8 |    7 |    4 |    4 |
|    4 |          9 |    7 |    4 |    4 |
|    4 |         10 |    7 |    4 | NULL |
+------+------------+------+------+------+
*/

4. Lateral derived tables

파생 테이블 앞에 LATERAL 키워드를 추가하여 동일한 FROM 절에서 이전 테이블의 컬럼을 참조 가능

세부 정보

SELECT class_name, roster_class.cnt 
FROM class, 
LATERAL 
(SELECT COUNT(*) AS cnt 
FROM roster 
WHERE roster.class_num=class.class_num) 
AS roster_class;

/*
+------------+-----+
| class_name | cnt |
+------------+-----+
| class1     |   2 |
| class2     |   1 |
| class3     |   1 |
| class4     |   1 |
| class5     |   0 |
+------------+-----+
*/

5. Aliases in single-table DELETE statements

단일 테이블 DELETE 문에 테이블 alias 사용 지원

6. Regular expression support

ICU(International Components for Unicode)를 사용하여 정규식 지원

• 전체 유니코드 지원하고, 멀티바이트에 안전

7. C API

DBMS 서버와의 비차단 통신을 위한 비동기 함수 지원

8. Additional target types for casts

CAST(), CONVERT() 함수는 DOUBLE, FLOAT, REAL 타입으로 형변형 지원

9. JSON schema validation

JSON 도큐먼트를 다시 JSON 스키마로 검증하기 위해 JSON_SCHEMA_VALID(), JSON_SCHEMA_VALIDATION_REPORT() 함수 추가

• JSON_SCHEMA_VALID() : JSON 스키마에 대해 JSON 도큐먼트의 유효성을 검사하여 TRUE(1) 또는 FALSE(0) 반환

• JSON_SCHEMA_VALIDATION_REPORT() : 유효성 검사 결과에 대한 자세한 정보가 포함된 JSON 도큐먼트를 반환

세부 정보

# JSON_SCHEMA_VALID
SELECT JSON_SCHEMA_VALID(
  '{
  "type":"object",
  "properties":{
  	"latitude":{"type":"number", "minimum":-90, "maximum":90},
  	"longitude":{"type":"number", "minimum":-180, "maximum":180}
  },
  "required": ["latitude", "longitude"]
  }', '{"latitude":59, "longitude":18}') as IS_VALID;

  /*
  +----------+
  | IS_VALID |
  +----------+
  |        1 |
  +----------+
  */


# JSON_SCHEMA_VALIDATION_REPORT
SELECT JSON_SCHEMA_VALIDATION_REPORT(
  '{
  "type":"object",
  "properties":{
  	"latitude":{"type":"number", "minimum":-90, "maximum":90},
  	"longitude":{"type":"number", "minimum":-180, "maximum":180}
  },
  "required": ["latitude", "longitude"]
  }', '{"latitude":91, "longitude":0}') as VALIDATION_REPORT;

# {"valid": false, "reason": "The JSON document location '#/latitude' failed requirement 'maximum' at JSON Schema location '#/properties/latitude'", "schema-location": "#/properties/latitude", "document-location": "#/latitude", "schema-failed-keyword": "maximum"}

CREATE TABLE geo (
  coordinate JSON,
  CHECK(
    JSON_SCHEMA_VALID(
      '{
        "type":"object",
  	  "properties":{
  	    "latitude":{"type":"number", "minimum":-90, "maximum":90},
  	    "longitude":{"type":"number", "minimum":-180, "maximum":180}
  	  },
  	  "required": ["latitude", "longitude"]
      }',
      coordinate
    )
  )
);


# 유효한 값
INSERT INTO geo VALUES('{"latitude":59, "longitude":18}');

# 유효하지 않은 값
INSERT INTO geo VALUES('{"latitude":91, "longitude":0}');
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.

# 유효하지 않은 값
INSERT INTO geo VALUES('{"longitude":120}');
ERROR 3819 (HY000): Check constraint 'geo_chk_1' is violated.

10. Multi-valued indexes

JSON 컬럼에 정의된 다중 값 인덱스 생성을 지원

• secondary index

• JSON 도큐먼트 작업을 위한 새로운 함수 JSON_OVERLAPS()와 MEMBER OF() 연산자를 추가하고 CAST() 함수를 새로운 ARRAY 키워드로 확장

세부 정보

CREATE TABLE customers (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  custinfo JSON
);

ALTER TABLE customers ADD INDEX zips( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

# 복합 인덱스도 가능
ALTER TABLE customers ADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

INSERT INTO customers VALUES
  (NULL, NOW(), '{"user":"Jack","user_id":37,"zipcode":[94582,94536]}'),
  (NULL, NOW(), '{"user":"Jill","user_id":22,"zipcode":[94568,94507,94582]}'),
  (NULL, NOW(), '{"user":"Bob","user_id":31,"zipcode":[94477,94507]}'),
  (NULL, NOW(), '{"user":"Mary","user_id":72,"zipcode":[94536]}'),
  (NULL, NOW(), '{"user":"Ted","user_id":56,"zipcode":[94507,94582]}');

# JSON_OVERLAPS() - 공통 키-값 쌍 또는 배열 요소가 있는 경우 출력
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));

/*
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  1 | 2023-04-10 16:10:32 | {"user": "Jack", "user_id": 37, "zipcode": [94582, 94536]}        |
|  2 | 2023-04-10 16:10:32 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2023-04-10 16:10:32 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2023-04-10 16:10:32 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
*/

# MEMBER OF() - value가 json_array의 요소이면 출력

SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');

/* 
+----+---------------------+-------------------------------------------------------------------+
| id | modified            | custinfo                                                          |
+----+---------------------+-------------------------------------------------------------------+
|  2 | 2023-04-10 16:10:32 | {"user": "Jill", "user_id": 22, "zipcode": [94568, 94507, 94582]} |
|  3 | 2023-04-10 16:10:32 | {"user": "Bob", "user_id": 31, "zipcode": [94477, 94507]}         |
|  5 | 2023-04-10 16:10:32 | {"user": "Ted", "user_id": 56, "zipcode": [94507, 94582]}         |
+----+---------------------+-------------------------------------------------------------------+
*/

11. EXPLAIN ANALYZE Statement

DBMS에서 구현되어, SELECT 문 실행에 대한 확장된 정보를 제공하고 예상 비용을 쿼리의 실제 비용과 비교 가능

• FORMAT=TREE 지정자 지원

12. Time zone support for TIMESTAMP and DATETIME

날짜/시간(TIMESTAMP 및 DATETIME) 값에 표준 시간대 오프셋을 허용

• datetime 값을 조회할 때 표준 시간대 오프셋이 표시되지 않음

• 표준 시간대 오프셋을 포함하는 Datetime 리터럴을 prepared statement의 매개 변수 값으로 사용 가능

세부 정보

CREATE TABLE t1 (tz_offset datetime);

INSERT INTO t1 VALUES ('2019-12-11 10:40:30-05:00');
INSERT INTO t1 VALUES ('2003-04-14 03:30:00+10:00');
INSERT INTO t1 VALUES ('2020-01-01 15:35:45+05:30');

SELECT tz_offset FROM t1;

/*
+---------------------+
| tz_offset           |
+---------------------+
| 2019-12-12 00:40:30 |
| 2003-04-14 02:30:00 |
| 2020-01-01 19:05:45 |
+---------------------+
*/

13. Row and column aliases with ON DUPLICATE KEY UPDATE

alias를 사용하여 삽입할 행과 해당 컬럼(선택 사항)을 참조 가능

세부 정보

INSERT INTO t1 SET col1=9,col2=5 AS new ON DUPLICATE KEY UPDATE col1=new.col1+new.col2;

INSERT INTO t1 VALUES(9,5) AS new ON DUPLICATE KEY UPDATE col1=new.col1+new.col2;

INSERT INTO t1 SET col1=9,col2=5 AS new(m,n) ON DUPLICATE KEY UPDATE col1=m+n;

INSERT INTO t1 VALUES(9,5) AS new(m,n)ON DUPLICATE KEY UPDATE col1=m+n;

14. SQL standard explicit table clause and table value constructor

SQL 표준에 따라 테이블 값 생성자와 명시적 테이블 절을 추가하여 DBMS에서 각각 TABLE 문과 VALUES 문으로 구현

• TABLE 문은 TABLE table_name 형식을 가지며 SELECT * FROM table_name과 동일

• VALUES는 INSERT, REPLACE 또는 SELECT 문에 테이블 값을 제공하는 데 사용할 수 있으며 VALUES 키워드와 쉼표로 구분된 일련의 생성자 ROW()로 구성

세부 정보

SELECT * FROM t1 UNION SELECT * FROM t2
=> TABLE t1 UNION TABLE t2

CREATE TABLE t2 SELECT * FROM t1
=> CREATE TABLE t2 TABLE t1

SELECT a FROM t1 WHERE b > ANY (SELECT * FROM t2)
=> SELECT a FROM t1 WHERE b > ANY (TABLE t2)

15. JSON_VALUE() function

DBMS에서 JSON 컬럼의 인덱싱을 단순화하기 위한 새로운 함수 JSON_VALUE()를 구현

세부 정보

SELECT CAST(
  JSON_UNQUOTE( JSON_EXTRACT('{"item": "shoes", "price": "49.95"}', '$.price') )
  AS DECIMAL(4,2)
);

/*
+-------+
| price |
+-------+
| 49.95 |
+-------+
*/


SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price' RETURNING DECIMAL(4,2)) AS price;

/*
+-------+
| price |
+-------+
| 49.95 |
+-------+
*/

16. XML enhancements

LOAD XML 문은 XML 파일의 CDATA 섹션을 지원

세부 정보

<?xml version="1.0" encoding="UTF-8"?>
<dictionary>
  <term>
      <entry>볼드</entry>
      <description>
          글씨를 굵은 글씨로 강조한다. <b>...</b> 태그를 사용한다. <b> 대신 <strong>을 사용해도 된다.
          <!-- 글씨를 굵은 글씨로 강조한다. ... 태그를 사용한다.  대신 을 사용해도 된다. -->
      </description>
      <description>
          <![CDATA[글씨를 굵은 글씨로 강조한다. <b>...</b> 태그를 사용한다. <b> 대신 <strong>을 사용해도 된다.]]>
          <!-- 글씨를 굵은 글씨로 강조한다. <b>...</b> 태그를 사용한다. <b> 대신 <strong>을 사용해도 된다. -->
      </description>
  </term>
</dictionary>

17. Casting to the YEAR type now supported

YEAR로의 형변형 가능

• CAST(), CONVERT() 함수는 모두 한 자리, 두 자리, 네 자리 YEAR 값을 지원

  • 한 자리, 두 자리 값의 경우 허용되는 범위는 0-99

  • 네 자리 값은 1901-2155 범위

• 문자열, 시간 및 날짜 및 부동 소수점 값은 모두 YEAR로 형변형 가능

• GEOMETRY 값은 불가능

세부 정보

SELECT CAST("11:35:00" AS YEAR), CAST(TIME "11:35:00" AS YEAR);

/* DATE, DATETIME, TIMESTAMP 는 값의 YEAR 부분, TIME은 현재 연도 반환
+--------------------------+-------------------------------+
| CAST("11:35:00" AS YEAR) | CAST(TIME "11:35:00" AS YEAR) |
+--------------------------+-------------------------------+
|                     2011 |                          2023 |
+--------------------------+-------------------------------+
*/

SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR);

/* DECIMAL, DOUBLE, DECIMAL, REAL 은 값을 가장 가까운 정수로 반올림한 후 정수 값 반환
+-----------------------+-----------------------+
| CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) |
+-----------------------+-----------------------+
|                  1944 |                  1945 |
+-----------------------+-----------------------+
*/

SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR);

/* 1-69 범위에 있으면 2000을 더하고, 70-99 범위에 있으면 1900을 더하여 반환
+---------------------+---------------------+
| CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) |
+---------------------+---------------------+
|                2066 |                2067 |
+---------------------+---------------------+
*/

18. Retrieval of TIMESTAMP values as UTC

시스템 시간대에서 UTC DATETIME으로 TIMESTAMP 컬럼 값의 변환을 지원

• CAST(value AT TIME ZONE specifier AS DATETIME) 사용

세부 정보

SELECT @@system_time_zone;
/*
+--------------------+
| @@system_time_zone |
+--------------------+
| KST                |
+--------------------+
*/

CREATE TABLE tz (c TIMESTAMP);

INSERT INTO tz VALUES ROW(CURRENT_TIMESTAMP);

SELECT * FROM tz;
/*
+---------------------+
| c                   |
+---------------------+
| 2023-04-11 10:15:53 |
+---------------------+
*/

SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz;
/*
+---------------------+
| u                   |
+---------------------+
| 2023-04-11 01:15:53 |
+---------------------+
*/

19. RIGHT JOIN as LEFT JOIN handling

내부적으로 RIGHT JOIN을 LEFT JOIN으로 처리

• 구문 분석 시 완전한 변환이 수행되지 않는 여러 특수한 경우를 제거

세부 정보

SELECT roster.roster_num, class.class_name
FROM roster
RIGHT JOIN class 
  ON roster.class_num = class.class_num;

/*
EXPLAIN FORMAT=TREE
-> Nested loop left join  (cost=2.50 rows=5)
  -> Table scan on class  (cost=0.75 rows=5)
  -> Covering index lookup on roster using idx_class_num (class_num=class.class_num)  (cost=0.27 rows=1)
*/

20. Conditional routine and trigger creation statements

IF NOT EXISTS 옵션 지원

• CREATE FUNCTION

• CREATE PROCEDURE

• CREATE TRIGGER

21. Nesting with UNION

괄호로 묶인 쿼리 식의 본문은 UNION과 조합하여 최대 63단계까지 중첩 가능

• 괄호로 묶인 쿼리 식의 본문을 축소할 때 SQL 표준 의미 체계를 따르므로 더 높은 외부 제한이 내부 더 낮은 제한을 재정의할 수 없음

  • (SELECT ... LIMIT 5) LIMIT 10 은 5개 이상의 결과를 반환할 수 없음

22. INTERSECT and EXCEPT table operators

INTERSECT, EXCEPT 테이블 연산자 추가