MySQL에서 ENUM 타입 칼럼에 멤버를 더 추가하려면 어떻게합니까?

MySQL에서 ENUM 타입 칼럼에 멤버를 더 추가하려면 어떻게합니까?

---

MySQL 참조 매뉴얼은이를 수행하는 방법에 대한 명확한 예를 제공하지 않습니다.

국가를 추가해야하는 ENUM 유형의 국가 이름 열이 있습니다. 이것을 달성하기위한 올바른 MySQL 구문은 무엇입니까?

내 시도는 다음과 같습니다.

ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia');

내가 얻는 오류는 다음과 같습니다. ERROR 1265 (01000): Data truncated for column 'country' at row 1.

country열은 위의 문에서 ENUM 타입의 열입니다.

테이블 출력 생성 방법 :

mysql> SHOW CREATE TABLE carmake;
+---------+---------------------------------------------------------------------+
| Table   | Create Table
+---------+---------------------------------------------------------------------+
| carmake | CREATE TABLE `carmake` (
`carmake_id` tinyint(4) NOT NULL AUTO_INCREMENT,
`name` tinytext,
`country` enum('Japan','USA','England','Australia','Germany','France','Italy','Spain','Czech Republic','China','South Korea','India') DEFAULT NULL,
PRIMARY KEY (`carmake_id`),
KEY `name` (`name`(3))
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1 |
+---------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

carmake 출력 에서 고유 한 국가를 선택하십시오 .

+----------------+
| country        |
+----------------+
| Italy          |
| Germany        |
| England        |
| USA            |
| France         |
| South Korea    |
| NULL           |
| Australia      |
| Spain          |
| Czech Republic |
+----------------+

---

ALTER TABLE
    `table_name`
MODIFY COLUMN
    `column_name2` enum(
        'existing_value1',
        'existing_value2',
        'new_value1',
        'new_value2'
    )
NOT NULL AFTER `column_name1`;

---

귀하의 코드가 저에게 효과적입니다. 내 테스트 사례는 다음과 같습니다.

mysql> CREATE TABLE carmake (country ENUM('Canada', 'United States'));
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE carmake;
+---------+-------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                            |
+---------+-------------------------------------------------------------------------------------------------------------------------+
| carmake | CREATE TABLE `carmake` (
  `country` enum('Canada','United States') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia');
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE carmake;
+---------+--------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                       |
+---------+--------------------------------------------------------------------------------------------------------------------+
| carmake | CREATE TABLE `carmake` (
  `country` enum('Sweden','Malaysia') default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

어떤 오류가 나타 납니까?

다음과 같이 작동합니다.

ALTER TABLE carmake MODIFY COLUMN country ENUM('Sweden','Malaysia');

실제로 열거 형 열이 아닌 국가 테이블을 권장합니다. 다소 크고 어색한 열거 형을 만드는 수백 개의 국가가있을 수 있습니다.

편집 : 이제 오류 메시지가 표시됩니다.

ERROR 1265 (01000): Data truncated for column 'country' at row 1.

I suspect you have some values in your country column that do not appear in your ENUM. What is the output of the following command?

SELECT DISTINCT country FROM carmake;

ANOTHER EDIT: What is the output of the following command?

SHOW VARIABLES LIKE 'sql_mode';

Is it STRICT_TRANS_TABLES or STRICT_ALL_TABLES? That could lead to an error, rather than the usual warning MySQL would give you in this situation.

YET ANOTHER EDIT: Ok, I now see that you definitely have values in the table that are not in the new ENUM. The new ENUM definition only allows 'Sweden' and 'Malaysia'. The table has 'USA', 'India' and several others.

LAST EDIT (MAYBE): I think you're trying to do this:

ALTER TABLE carmake CHANGE country country ENUM('Italy', 'Germany', 'England', 'USA', 'France', 'South Korea', 'Australia', 'Spain', 'Czech Republic', 'Sweden', 'Malaysia') DEFAULT NULL;

---

The discussion I had with Asaph may be unclear to follow as we went back and forth quite a bit.

I thought that I might clarify the upshot of our discourse for others who might face similar situations in the future to benefit from:

ENUM-type columns are very difficult beasts to manipulate. I wanted to add two countries (Malaysia & Sweden) to the existing set of countries in my ENUM.

It seems that MySQL 5.1 (which is what I am running) can only update the ENUM by redefining the existing set in addition to what I want:

This did not work:

ALTER TABLE carmake CHANGE country country ENUM('Sweden','Malaysia') DEFAULT NULL;

The reason was that the MySQL statement was replacing the existing ENUM with another containing the entries 'Malaysia' and 'Sweden' only. MySQL threw up an error because the carmake table already had values like 'England' and 'USA' which were not part of the new ENUM's definition.

Surprisingly, the following did not work either:

ALTER TABLE carmake CHANGE country country ENUM('Australia','England','USA'...'Sweden','Malaysia') DEFAULT NULL;

It turns out that even the order of elements of the existing ENUM needs to be preserved while adding new members to it. So if my existing ENUM looks something like ENUM('England','USA'), then my new ENUM has to be defined as ENUM('England','USA','Sweden','Malaysia') and not ENUM('USA','England','Sweden','Malaysia'). This problem only becomes manifest when there are records in the existing table that use 'USA' or 'England' values.

BOTTOM LINE:

Only use ENUMs when you do not expect your set of members to change once defined. Otherwise, lookup tables are much easier to update and modify.

---

In MYSQL server version: 5.0.27 i tried this and it worked fine for me check in your version

ALTER TABLE carmake
     MODIFY `country` ENUM('Japan', 'USA', 'England', 'Australia', 'Germany', 'France', 'Italy', 'Spain', 'Czech Republic', 'China', 'South Korea', 'India', 'Sweden', 'Malaysia');

---

Here is another way...

It adds "others" to the enum definition of the column "rtipo" of the table "firmas".

set @new_enum = 'others';
set @table_name = 'firmas';
set @column_name = 'rtipo';
select column_type into @tmp from information_schema.columns 
  where table_name = @table_name and column_name=@column_name;
set @tmp = insert(@tmp, instr(@tmp,')'), 0, concat(',\'', @new_enum, '\'') );
set @tmp = concat('alter table ', @table_name, ' modify ', @column_name, ' ', @tmp);
prepare stmt from @tmp;
execute stmt;
deallocate prepare stmt;

---

FYI: A useful simulation tool - phpMyAdmin with Wampserver 3.0.6 - Preview SQL: I use 'Preview SQL' to see the SQL code that would be generated before you save the column with the change to ENUM. Preview SQL

Above you see that I have entered 'Ford','Toyota' into the ENUM but I am getting syntax ENUM(0) which is generating syntax error Query error 1064#

I then copy and paste and alter the SQL and run it through SQL with a positive result.

SQL changed

This is a quickfix that I use often and can also be used on existing ENUM values that need to be altered. Thought this might be useful.

---

It's possible if you believe. Hehe. try this code.

public function add_new_enum($new_value)
  {
    $table="product";
    $column="category";
         $row = $this->db->query("SELECT COLUMN_TYPE FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = ? AND COLUMN_NAME = ?", array($table, $column))->row_array();

    $old_category = array();
    $new_category="";
    foreach (explode(',', str_replace("'", '', substr($row['COLUMN_TYPE'], 5, (strlen($row['COLUMN_TYPE']) - 6)))) as $val)
    {
        //getting the old category first

        $old_category[$val] = $val;
        $new_category.="'".$old_category[$val]."'".",";
    }

     //after the end of foreach, add the $new_value to $new_category

      $new_category.="'".$new_value."'";

    //Then alter the table column with the new enum

    $this->db->query("ALTER TABLE product CHANGE category category ENUM($new_category)");
  }

Before adding new value

After adding new value

참고URL : https://stackoverflow.com/questions/1501958/how-do-i-add-more-members-to-my-enum-type-column-in-mysql