从索引mysql删除列

发布于 2025-02-09 22:18:21 字数 1531 浏览 2 评论 0原文

我的表格上有这些索引：

mysql> SHOW INDEXES from sous_categories;
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sous_categories |          0 | PRIMARY  |            1 | id_sous_categorie | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sous_categories |          0 | PRIMARY  |            2 | categorie_id      | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

我想通过检查 mySQL索引statment ，我发现我只能通过index_name删除，但就我而言，两者都具有相同的key_name。我该怎么做？（我知道这是可能的，因为我可以使用phpmyadmin做到这一点，但我想知道如何使用命令行进行）

原文

i'am having these indexes on my table:

mysql> SHOW INDEXES from sous_categories;
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table           | Non_unique | Key_name | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sous_categories |          0 | PRIMARY  |            1 | id_sous_categorie | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| sous_categories |          0 | PRIMARY  |            2 | categorie_id      | A         |          16 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------------+------------+----------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

and i want to remove the second column, by checking MySQL INDEX STATMENT, i figured out that i can drop only by index_name, but in my case both has the same key_name.
How can i do that please ? (I know it's possible because i can do it with phpmyadmin but i want to know how to do it with command line)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

最近可好 2025-02-16 22:18:22

ALTER TABLE sous_categories
    DROP PRIMAR KEY,
    ADD PRIMARY KEY(id_sous_categorie);

但是，要成为pk，id_sous_categorie必须是唯一的（无dups）和而不是null。

唯一的效果将是

花时间重建桌子。（对PK的任何更改都需要重建。）
如果ID_SOUS_CATEGORIE还不是唯一的，则致命错误。（以前，一对列被限制为唯一，但每列不是。）

ALTER TABLE sous_categories
    DROP PRIMAR KEY,
    ADD PRIMARY KEY(id_sous_categorie);

However, to be the PK, id_sous_categorie must be Unique (no dups) and NOT NULL.

The only effects will be

Time spent rebuilding the table. (Any change to the PK requires a rebuild.)
A fatal error if id_sous_categorie is not already unique. (Previously the pair of columns was constrained to be unique, but not each column.)

回复收藏 0 原文

~没有更多了~