mysql - 如何在mysql中的CONCAT ("string"，列)上创建索引？

Question

我有一个表，其中 id 是主键。

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT,
  col1 VARCHAR(45) NULL,
  PRIMARY KEY (id));

我有另一个表 t2，它将表 t1 连接为

t2 LEFT JOIN t1 ON CONCAT("USER_", t1.id) = t2.user_id

我想创建一个索引，其中 CONCAT("USER_", t1.id) 值以任意顺序编制索引。

我试过了

ALTER TABLE t1 ADD INDEX ((CONCAT('user_',id) DESC);

但它给出了错误。我遵循了mysql的官方文档。

注意:我不想创建新的 CONCAT("user_", id) 列。

https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-column-prefixes

Answer 1

InnoDB supports secondary indexes on virtual generated columns. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

在 5.7(以后)中，您可以使用生成的列，然后对该列建立索引。例如

以下是从字符串中取出整数以创建有效连接的示例:

CREATE TABLE myusers (
    id mediumint(8) unsigned NOT NULL auto_increment
  , name varchar(255) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1
;

INSERT INTO myusers (`name`) VALUES ('Imelda'),('Hamish'),('Brandon'),('Amity'),('Jillian'),('Lionel'),('Faith'),('Dai'),('Reed'),('Molly');

CREATE TABLE mytable (
    id mediumint(8) unsigned NOT NULL auto_increment
  , user_id VARCHAR(20)
  , ex_user_id integer GENERATED ALWAYS AS (0+substring(user_id,6,20))
  , password varchar(255)
  , PRIMARY KEY (`id`)
  , INDEX idx_ex_user_id (ex_user_id)
) AUTO_INCREMENT=1
;

INSERT INTO mytable (`user_id`,`password`) VALUES 
 ('user_1','PYX68BIC9RD')
,('user_2','LPY07EIN0UA')
,('user_3','UGC24TKI3JL')
,('user_4','YQU18ALB8YA')
,('user_5','DEL56AGR6AD')
,('user_6','YQN87UOB0PO')
,('user_7','CPC15JFU6MC')
,('user_8','MWC40ZWD2EE')
,('user_9','HEB34QQH0UM')
,('user_10','GVP36PLP5PW')
;

select
*
from myusers
inner join mytable on myusers.id = mytable.ex_user_id
;

id | name    | id | user_id | ex_user_id | password   -: | :------ | -: | :------ | ---------: | :---------- 1 | Imelda  |  1 | user_1  |          1 | PYX68BIC9RD 2 | Hamish  |  2 | user_2  |          2 | LPY07EIN0UA 3 | Brandon |  3 | user_3  |          3 | UGC24TKI3JL 4 | Amity   |  4 | user_4  |          4 | YQU18ALB8YA 5 | Jillian |  5 | user_5  |          5 | DEL56AGR6AD 6 | Lionel  |  6 | user_6  |          6 | YQN87UOB0PO 7 | Faith   |  7 | user_7  |          7 | CPC15JFU6MC 8 | Dai     |  8 | user_8  |          8 | MWC40ZWD2EE 9 | Reed    |  9 | user_9  |          9 | HEB34QQH0UM10 | Molly   | 10 | user_10 |         10 | GVP36PLP5PW

explain select
*
from myusers
inner join mytable on myusers.id = mytable.ex_user_id
;

id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref                                    | rows | filtered | Extra      -: | :---------- | :------ | :--------- | :--- | :------------- | :------------- | :------ | :------------------------------------- | ---: | -------: | :---------- 1 | SIMPLE      | myusers | null       | ALL  | PRIMARY        | null           | null    | null                                   |   10 |   100.00 | null        1 | SIMPLE      | mytable | null       | ref  | idx_ex_user_id | idx_ex_user_id | 5       | fiddle_HNTHMETRTFAHHKBIGWZM.myusers.id |    1 |   100.00 | Using where

db<> fiddle here

注意 user_id 从字符串到整数的转换是“隐式”的:

To cast a string to a number, you normally need do nothing other than use the string value in numeric context: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html