在枚举字段上设置索引 [英] Setting the index on enum fields

问题描述

我有一个MySQL数据库，当前使用tinyint字段存储记录类型.它使用奇数编号，我们正在更改中.因此，目前显示为:

I have a MySQL database which currently uses a tinyint field to store the type of record. It uses an odd numbering, and we are in the process to change this. So currently, it reads:

record type
1      60
2      62
3      60
4      61
5      63

数字代表类型，

60 = car
61 = bike
62 = motorcycle

我们需要使用值car，bike，motorcycle将其转换为枚举字段.但是，正在使用的表有大量的页面，其中使用原始数字进行SELECT和INSERT.那将逐渐改变.所以我想知道:是否有办法更改enum字段的索引，以便我可以将字段更改为enum并仍保存类型= 60的新记录，而这变成了"car"?我知道有一种方法可以将其设置为0，它将变成汽车，但是很明显，这需要我们更改所有页面，并且没有时间这样做……

We need to transform this into an enum field, using the values car, bike, motorcycle. However, the table in question in being used from a very large number of pages, where SELECTs and INSERTs are being made using the original numbers. That will change gradually. So I was wondering: is there a way to change the indexes of the enum field, so that I could change the field to enum and still save a new record with type = 60, and this becomes 'car'? I know there is a way to set it to 0 and it will become car, but clearly that requires us to change all of the pages and there is no time for this...

推荐答案

不幸的是，这是不可能的. 手册本身没有希望的余地:

Unfortunately this is not possible. The manual itself leaves no room for hope:

每个枚举值都有一个索引:

Each enumeration value has an index:

•列中允许的元素列表中的值 规范从1开始编号.

• Values from the list of permissible elements in the column specification are numbered beginning with 1.

鉴于您对此无能为力，我不得不问:为什么要将它设为enum?我还没有遇到这样的情况:enum提供了 any ，这给那些通过眼睛查看查询结果的程序员带来了极大的便利.您希望获得什么好处?我不是专家，但是恕我直言，无论您想要实现什么，除了使用enum之外，还有另一种(更好的)方法.

Seeing as there's nothing you can do about that, I have to ask: why do you need to make this an enum? I have yet to come across a situation where an enum provided any benefit apart convenience to programmers who look at query results by eye. What is the benefit you are looking to gain? I am no guru, but IMHO no matter what you want to achieve there is another (better) way than using an enum.

这篇关于在枚举字段上设置索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！