SQL - Statements

資料庫 22 分钟 讀完 (大概 3311 字)

# 前言

本篇主要為細讀官方文件後, 整理為 Q&A 的原子化翻譯筆記



Data Definition Statements

# CREATE INDEX Statement

# Column Prefix Key Parts

MySQL 中, CREATE INDEX 可以建立 primary key index 嗎？

不行

以下的 MySQL example code 的意思是?
  • Example:
    CREATE INDEX part_of_name ON customer (name(10));
  • Answer:
    在 customer table 建立一個 INDEX 名為 part_of_name, prefix 為 10
MySQL 中, 有哪六種 column type 可以指定 index prefix length?

CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT

MySQL 中, 有哪兩種 column type 必定要使用 index prefix length 當建立 index 時?

TEXT, BLOB

MySQL 中, index prefix length 的計算方式隨著 column type 有所不同, 以下的 column type 會使用什麼單位計算, character or byte?
  • Example:
    CHAR, VARCHAR, TEXT
  • Answer:
    character, 每個 character 等於幾 byte 視乎 character 而有所不同, 通常英文字母為 1 byte 就夠, 中文字甚至要到 3-4 bytes
MySQL 中, index prefix length 的計算方式隨著 column type 有所不同, 以下的 column type 會使用什麼單位計算, character or byte?
  • Example:
    BINARY, VARBINARY, BLOB
  • Answer:
    Byte
MySQL 中, index prefix length 的長度限制因 row format 而有所不同, 以下的 row format 的 index prefix length 長度限制是多少？
  • Example:
    REDUNDANT, COMPACT
  • Answer:
    767 bytes
MySQL 中, index prefix length 的長度限制因 row format 而有所不同, 以下的 row format 的 index prefix length 長度限制是多少？
  • Example:
    DYNAMIC, COMPRESSED
  • Answer:
    3072 bytes
MySQL 中, 如果一個指定的 ununique INDEX prefix 超出了限制, 且 strict SQL mode enabled, 那會如何？

噴錯

MySQL 中, 如果一個指定的 ununique INDEX prefix 超出了限制, 但 strict SQL mode unenabled, 那會如何？

超出的長度會被截斷, 產生 warning

MySQL 中, 如果一個指定的 unique INDEX prefix 超出了限制, 那會如何？

不管 strict SQL mode 是否 enabled 都會噴錯, 因為如果只儲存部分 unique index 字串, 那就不符合 unique 規則

MySQL 中, 在什麼情況下, prefixed index 在效能上不會差正常 index 太多？

當欄位內容的 prefix 長度字串都不一樣時, 比如說, prefix 為 10, 那如果每個 row 的 column value 前十個字都不同的話, 其實效能上跟一般 index 是差不多的

MySQL 中, 在什麼情況下, 適合使用 prefixed index?

當這個 column value 前面 prefix 長度的字串幾乎都不同時

MySQL 中, prefix index 有什麼優點？

可以節省 disk space, 以及提升 insert 效能


# Functional Key Parts

以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE t1 (
      col1 VARCHAR(10),
      col2 VARCHAR(20),
      INDEX (col1, col2(10))
    );
  • Answer:
    該 INDEX 包含了 col1 value 以及 col2 prefix 10 個 character
以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
    CREATE INDEX idx1 ON t1 ((col1 + col2));
    CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
    ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
  • Answer:
    ## 建立 INDEX with function key parts, 只儲存 expression 不儲存 column value
    CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
    CREATE INDEX idx1 ON t1 ((col1 + col2));
    ## multi functional key pars 也可同時包含 functional 以及 nonfunctional
    CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
    ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
以下的 MySQL example code 是正確的嗎? 如果不是, 為什麼？
  • Example:
    INDEX (col1 + col2, col3 - col4)
  • Answer:
    不正確
    function expression 必須要被包在 parentheses 中
以下的 MySQL example code 是正確的嗎? 如果不是, 為什麼？
  • Example:
    INDEX ((col1), (col2))
  • Answer:
    不正確
    function key part 必須是一個 function expression, 不能單單是一個 column name
MySQL 中, INDEX with function key parts 可以用來定義 foreign key INDEX 嗎？

不行

MySQL 中, Index with functional key parts 支援以下的語法嗎？
  • Example:
    subquery, parameters, variables, stored functions, user-defined functions
  • Answer:
    不支援
MySQL 中, UNIQUE key INDEX 可以使用 functional key parts 嗎？

可以

MySQL 中, PRIMARY key INDEX 可以使用 functional key parts 嗎？

不行, 因為 function key parts 是用 virtual generated column 實作, 而 primary key 會需要 stored generated column

MySQL 中, SPATIAL 和 FULLTEXT INDEX 支援 functional key parts 嗎？

不支援

MySQL 中, 當一個 table 中沒有 primary key 時, 會使用哪個 INDEX 當 primary key?

First UNIQUE INDEX NOT NULL

MySQL 中, 當一個 table 中沒有 primary key 時, 會使用 First Unique Index Not Null 來當 primary key, 那如果該 INDEX 含有 functional key parts 的話, 支援嗎？

不支援

MySQL 中, INDEX with nonfunctional key parts 當出現重複時, 會 warning, 那 INDEX with functional key parts 也有這個特性嗎?

沒有

MySQL 中, 如果我要刪除一個被 INDEX with functional key parts refer 的 column, 需要先刪除 INDEX 嗎？

需要

以下的 MySQL example code, 哪一個 query 會使用到 INDEX idx1??
  • Example:
    CREATE TABLE tbl (
      col1 LONGTEXT,
      INDEX idx1 ((SUBSTRING(col1, 1, 10)))
    );
    SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
    SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';
  • Answer:
    第二個
    當 INDEX with functional key parts 使用 SUBSTRING 時, where clause 需符合 INDEX
以下的 MySQL example code 中, syntax 正確嗎？ 為什麼
  • Example:
    CREATE TABLE employees (
      data JSON,
      INDEX ((data->>'$.name'))
    );
  • Answer:
    不正確
    ->> 代表 JSON_UNQUOTE(JSON_EXTRACT())
    JSON_UNQUOTE 會 return value with data type LONGTEXT, 所以 virtual generated column 也會是這個 data type
    MySQL 若要 INDEX LONGTEXT, 一定要使用 prefix length
MySQL 中, JSON_UNQUOTE return 的 string collation 是？

utfmb4_binary (hardcoded)

MySQL 中, CAST() return 的 string collation 是？

utf8mb4_0900_ai_ci (server_default_collation)

以下的 MySQL example code, 會產生什麼問題?
  • Example:
    CREATE TABLE employees (
      data JSON,
      INDEX ((CAST(data->>'$.name' AS CHAR(30))))
    );

    SELECT * FROM employees WHERE data->>'$.name' = 'James';
  • Answer:
    data->>'$.name' 會取得 string with collation utf8mb4_binary(hardcoded)
    CAST([input] AS [format or collation]) 會上面的輸出轉為 collation 為 utf8mb4_0900_ai_ci 的 string, 並存在 virtual generated column VARCHAR(30)
    然而, where data->>'$.name' 這邊的 collation 依然是 utf8mb4_binary, 如果 functional index 的輸出 collation 跟 where clause 的不同, 就 不會使用到 index
以下的 MySQL example code 會輸出哪些 records, 為什麼？
  • Example:
    CREATE TABLE employees (
      data JSON,
      INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
    );
    INSERT INTO employees VALUES
      ('{ "name": "james", "salary": 9000 }'),
      ('{ "name": "James", "salary": 10000 }'),
      ('{ "name": "Mary", "salary": 12000 }'),
      ('{ "name": "Peter", "salary": 8000 }');
    SELECT * FROM employees WHERE data->>'$.name' = 'James';
  • Answer:
    雖然特別使用 COLLATE 將 collation 轉為 utf8mb4_bin, 與 where clause 中的相同, 但因為 collation bin, 所以會是 case-sensitive
以下的 MySQL example code 會輸出哪些 records, 為什麼？
  • Example:
    CREATE TABLE employees (
      data JSON,
      INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
    );
    INSERT INTO employees VALUES
      ('{ "name": "james", "salary": 9000 }'),
      ('{ "name": "James", "salary": 10000 }'),
      ('{ "name": "Mary", "salary": 12000 }'),
      ('{ "name": "Peter", "salary": 8000 }');
    SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';
  • Answer:
    index 跟 where clause 的 collation 都是 CAST() 輸出的 utf8mb4_0900_ai_ci, 所以結果會是 case-insensitive
以下的 MySQL example code 的意思是?
  • Example1:
    CREATE TABLE employees (
      data JSON,
      generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
    );

    INSERT INTO employees (data)
    VALUES ('{"name": "james"}'), ('{"name": "James"}');

    SELECT * FROM employees WHERE data->>'$.name' = 'James';
  • Output of example1:
  • Example2:
    ALTER TABLE employees ADD INDEX idx (generated_col);

    SELECT * FROM employees WHERE data->>'$.name' = 'James';
  • Output of example2:
  • Answer:
    example 1 中, 就算沒有 CAST(), generated_col 也是吃 column 的 default collation, 然而, 因為沒有 INDEX, JSON_UNQUOTE 輸出的 string 的 collation 一律為 utf8mb4_bin, 所以結果會是 case-sensitive
    example 2 中, 如上面提到, generated_col 的 collation 為 column 預設的 collation, 假設為 utf8mb4_0900_ai_ci, 因為有新增了 INDEX, 所以會使用到 generated column 的 collation, 因此會變成 case-insensitive, 但這視乎 generated_col 的 collation, 如果我們在建立這個 column 時便指定 collation 為 utfmb4_bin, 那結果會是 case-sensitive

# Unique Indexes

MySQL 中, UNIQUE INDEX value 可以重複嗎？

不行

MySQL 中, UNIQUE INDEX 允許 NULL value 嗎？

如果該 column 是 nullable, 是允許的

MySQL 中, 如果 PRIMARY_KEY 存在, 但 PRIMARY_KEY INDEX 並不含有 single integer column, _rowid 可被使用嗎？
  • Answer:
    不可
  • Illustration:

MySQL 中, 如果 PRIMARY_KEY 存在, 且 PRIMARY_KEY INDEX 含有 single integer column, _rowid 會參照這個 column 嗎？
  • Answer:
    會
  • Illustration:

MySQL 中, 如果 FIRST NOT NULL UNIQUE INDEX 存在, 且含有 single integer column, _rowid 會參照這個 column 嗎？

會哦

MySQL 中, 如果 FIRST NOT NULL UNIQUE INDEX 存在, 但不含有 single integer column, _rowid 可被使用嗎？

不能


# Full-Text Indexes

MySQL 中, FULLTEXT INDEX 只可使用在哪些 column type？

CHAR, VARCHAR, TEXT


# Multi-Valued Indexes

MySQL 中, multi-valued index 跟 normal index 差異在於？

normal index: 一筆 index value 對應一筆 row data
multi-valued index: 多筆 index value 對應一筆 row data

MySQL 中, multi-valued index 主要是用在什麼 column type?

JSON column type

# Creating multi-values Indexes
以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON,
        INDEX zips( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) )
        );
  • Answer:
    建立一個 multi-valued INDEX zips
以下的 MySQL example code 的意思是?
  • Example:
    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->'$.zip' AS UNSIGNED ARRAY)) );
  • Answer:
    加入一個 multi-valued INDEX zips
以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON
        );

    CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zip' AS UNSIGNED ARRAY)) );
  • Answer:
    建立一個 multi-valued INDEX zips
以下的 MySQL example code 的意思是?
  • Example:
    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 comp(id, modified,
        (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
  • Answer:
    建立一個 composite index, 裡面包含 multi-valued INDEX
MySQL 中, 一個 composite index 可以含有幾個 multi-valued index?

一個

MySQL 中, multi-valued index 在 composite index 中的順序可以調換嗎？

可以

以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON,
        INDEX zips( (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]}');    

    SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode');
  • Answer:
    建立 zips multi-valued INDEX, 支援 MEMBER OF function, 取得 94507 有出現在 custinfo->$.zipcode array 中的 row
以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON,
        INDEX zips( (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]}');    

    SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
  • Answer:
    建立 zips multi-valued INDEX, 支援 JSON_CONTAINS function, 取得 94507 and 94582 有出現在 custinfo->$.zipcode array 中的 row
以下的 MySQL example code 的意思是?
  • Example:
    CREATE TABLE customers (
        id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        custinfo JSON,
        INDEX zips( (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]}');    

    SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode', CAST('[94507,94582]' AS JSON));
  • Answer:
    建立 zips multi-valued INDEX, 支援 JSON_OVERLAPS function, 取得 94507 or 94582 有出現在 custinfo->$.zipcode array 中的 row

# Characteristics of Multi-Valued Indexes
MySQL 中, 什麼是 DML?

Data Manipulation Language

MySQL 中, 如果 multi-valued key part 是一個 empty array, 那會產生相應的 INDEX 嗎?

不會

MySQL 中, 當我使用 multi-valued INDEX, JSON 可以含有 null 值嗎, 如下 example?
  • Example:
    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":null}');
  • Answer:
    不行, 會 Errors Invalid JSON value, [94536, null] 也不行
以下的 Laravel example code 的意思是？
  • Example:
    CAST(data->'$.arr[*][*]' AS UNSIGNED ARRAY)
  • Answer:
    雖說每一個 multi-valued index 只支援一個 multi-valued key part, 但可使用如上 example refer to 多個 array, 所有符合的 value 都會被儲存到 index, 以 flat array 的形式
MySQL 中, multi-valued index 支援 ordering 嗎？

不支援

MySQL 中, multi-valued index 可被當成 primary key 使用嗎？

不行

MySQL 中, multi-valued index 可以是一個 convering index 嗎？

不行

MySQL 中, 一個 row 的 multi-valued index 的 value 數量最多可達多少？

總長度為 65000 bytes

MySQL 中, 一個 row 的 multi-valued index 的 key 數量最多可達多少？

1604 個 key

MySQL 中, multi-valued index 只接受什麼樣的 expression？

JSON expression

MySQL 中, multi-valued index 使用的 JSON expression 需要指向一個 JSON document 實際上存在的 element 嗎？

不需要, syntax 正確就行

MySQL 中, multi-valued index 支援 range scan 或 index-only scan 嗎？

不支援

MySQL 中, multi-valued index 可使用 CAST 定義為 binary 嗎？

不可

MySQL 中, multi-valued index 支援 online creation 嗎？

不支援

MySQL 中, multi-valued index 只支援哪兩組 character set 與 collation

binary character set 與 default binary collation
utf8mb4 character set 與 default utf8mb4_0900_ai_ci collation


# CREATE TABLE Statement

# Secondary Indexes and Generated Columns

#MySQL #SQL Statements #Secondary Indexes
Kubernetes - Dashboard - 使用自定義 Service Account 登入 Kubernetes - RBAC - 建立一個只允許存取特定權限的使用者

留言

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×