MySQL将字段添加到枚举 [英] MySQL add fields to an Enum

问题描述

我必须向数据库表中添加一些枚举选项.问题是，我将不得不对几个表&执行此操作.数据库，并且它们可能不都具有相同的枚举数据类型.为什么要编写一个alter table查询或类似于将选项附加到enum数据类型的东西?

I have to add some enum options to a database table. The problem being, I will have to do this to several tables & databases, and they may not all have the same enum data type. Is there a why to write an alter table query or something similar to append options to a enum data type?

如果没有办法完全在MySQL中做到这一点，您将如何使用PHP脚本解决此问题?

If there is no way to do this purely in MySQL, how would you approach this problem with a PHP script?

推荐答案

没有简单的方法来附加枚举值.

there is not easy way to append enum values.

这是丑陋且未经测试的，但我认为它会给您一个想法:

this is ugly and untested, but i think it will give you an idea:

<?php

$sql = "select table_schema
             , table_name
             , column_name
             , column_type
             , is_nullable
             , column_default
          from information_schema
         where data_type = 'enum'";

$res = mysql_query($sql);

while ($row = mysql_fetch_assoc($res)) {

  // these are important -->       leading comma --v      close paren --v
  $new_enum = substr($row['column_type', 0, -1) . ",'new','enums','here')"

  $sql = "alter table `{$row['table_schema']}`.`{$row['table_name']}`";
  $sql .= " modify column `{$row['column_name']}`";
  $sql .= " $new_enum";
  $sql .= ($row['is_nullable'] == 'YES') ? " NULL" : " NOT NULL";
  $sql .= " default $row['column_default']";

  mysql_query($sql);
}

您可能可以使用存储过程仅在mysql中"执行此操作，但是这比我现在能动脑筋的要多. :)

you could probably do this "purely in mysql" with a stored procedure, but that's more than i can wrap my brain around right now. :)

这篇关于MySQL将字段添加到枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！