英文:
Is it possible to create a casted index on MySQL?
问题
尝试在表上创建一个转换索引（我的列的数据类型是char(64)），我的查询如下：
CREATE INDEX idx_column_name ON table (CAST(column_name AS INTEGER));
但是它没有成功，它给出了错误SQL Error [1064] [42000]，这意味着我的语法中有错误。我尝试了很多不同的语法，但仍然没有成功。是否有人能够明确指出问题出在哪里？
英文:
So I tried to create a casted index on a table (The data type of my column is char(64)), my query is like that:
CREATE INDEX idx_column_name ON table (CAST(column_name AS INTEGER));
But it didn't work, It gives the error SQL Error [1064] [42000] which means that I have an error in my syntax, I tried so many syntaxes but still doesn't work, Can anyone have a clear idea about what exactly the issue is?
答案1
得分: 1
你的语句中有2个错误。
INTEGER不是正确的数据类型，不能在CAST()中使用。应该使用SIGNED或UNSIGNED代替。- 表达式必须用单独的括号括起来。
因此，正确的语法如下：
CREATE TABLE table_name (column_name VARCHAR(64));
CREATE INDEX idx_column_name ON table_name ( (CAST(column_name AS UNSIGNED)) );
SHOW CREATE TABLE table_name;
| Table | Create Table |
|---|---|
| table_name | CREATE TABLE table_name (column_name varchar(64) DEFAULT NULL,KEY idx_column_name ((cast(column_name as unsigned)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| fiddle |
英文:
You have 2 errors in your statement.
INTEGERis not correct datatype which can be used in CAST(). Use SIGNED or UNSIGNED instead.- The expression must be enclosed with separate parenthesis.
So correct syntax is:
CREATE TABLE table_name (column_name VARCHAR(64));
CREATE INDEX idx_column_name ON table_name ( (CAST(column_name AS UNSIGNED)) );
SHOW CREATE TABLE table_name;
| Table | Create Table |
|---|---|
| table_name | CREATE TABLE `table_name` (<br> `column_name` varchar(64) DEFAULT NULL,<br> KEY `idx_column_name` ((cast(`column_name` as unsigned)))<br>) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论