MySQL enum field insert by index

Question

There is a simple mysql table 'mytable' with a column called 'fruit', which is a enum field contains 'apple', 'orange' and 'mango'.

We can access the index value of the enum with SELECT fruit+0 FROM mytable But how can we insert the enum field by index instead of INSERT INTO mytable fruit VALUE 'apple' WHERE ...

I tried INSERT INTO mytable fruit+0 VALUE '1' WHERE ... but it doesn't work and couldn't find any related topics here or from net.

Thanks.

piotrm · Accepted Answer · 2011-05-31 18:32:38Z

2

A simple INSERT INTO mytable(fruit) VALUES(1); should work.

You don't need any fruit+0 conversion like in select.

answered May 31, 2011 at 18:32

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wil Moore III Over a year ago

This answer is correct. You can simply use the numeric index as the value; however, be aware that you will need to keep track of the indexes yourself in case you re-order the enum values. They are indexed starting at 0 so your first item is at index 0. I will up vote this answer, but OP, please be aware, this isn't the greatest thing to do in practice.

babyromeo Over a year ago

Thanks! it works now. @wilmoore, in fact, the index will be generate at the beginning of access and no re-order of the enum values except if mysql will re-order it automatically?

MM. Over a year ago

Warning: ENUMs are 1-based, at least in MySQL: "The elements listed in the column specification are assigned index numbers, beginning with 1. The index value of the empty string error value is 0." dev.mysql.com/doc/refman/5.7/en/enum.html

Marc B · Accepted Answer · 2011-05-31 17:35:09Z

0

enums are specifically designed to remove the need to know index values. By using index values you're specifically going against the reason enums exist.

If you don't want to use the enum values for insertion, then I'd suggest splitting the fruit names off into a seperate table, and make your mytable.fruit a foreign key pointing at this new fruit table. Then you're inserting an ID value instead of 'apple'.

answered May 31, 2011 at 17:35

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

babyromeo Over a year ago

thanks for your answer and your explaination. i totally agree with you. but i want to know if there is a way doing that?

Collectives™ on Stack Overflow

MySQL enum field insert by index

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related