时间戳

首页 > 解决方案 > mysql 查询以显示为名为部门的列创建的所有枚举值？

mysql - mysql 查询以显示为名为部门的列创建的所有枚举值？

问题描述

我有一个名为employees 的基本表，其中包含以下列：

user_id PK
first_name
last_name
department enum('accounts', 'sales', 'marketing', 'customer support','billing', 'logistics', 'production') NULL is default.
project_history
email
password SHA1
registration_date

到目前为止，我创建了数据库，它包含一个表，“员工”。正如您从上面看到的，它是一个非常简单的直接表。

我想在 phpMyAdmin 的 sql 编辑器中快速轻松地运行 sql 查询，以便我可以轻松查看部门可用的枚举值/选项。我很感兴趣，因为我想记住创建了哪些值，我能够看到这些值的唯一方法是在 phpmyadmin 中进行结构。

当然，我可以运行一个 sql 查询来非常快速地查看为部门设置的枚举值？

如果是这样，我只是想知道它是什么。

无需共享 mysql 数据库或表 users，因为它在 wampserver 下本地运行，您可以按照上面提供的内容进行操作。

我希望查询返回类似于以下内容：

department
---------------
NULL - 0
accounts - 1
sales - 2
marketing - 3
customer support - 4
billing - 5
logistics - 6
production - 7

无论哪种方式，我只想查看分配给列部门的枚举值，而不管其显示方式如何。是否可以通过 sql 查询来完成，因为我非常肯定它可以。

标签： mysqlenums

解决方案

这个答案是为了教育目的，因为JNevill 在上面的评论中提出的建议更有 意义

这在纯 MySQL 代码中是可能的，但您必须编写棘手的 SQL。

部分答案包括一个 SQL 数字生成器

SELECT 
 (@number := @number + 1) AS number
FROM (    
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_1
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

看演示

另一部分是将枚举作为逗号分隔的字符串并知道项目的数量。

询问

SELECT
 REPLACE(
   REPLACE(
      REPLACE(COLUMN_TYPE, 'enum(', '')
      , ')'
      , ''
   )
   , "'"
   , ''
 ) AS comma_separated
 , LENGTH(
    REPLACE(
      REPLACE(
        REPLACE(COLUMN_TYPE, 'enum(', '')
        , ')'
        , ''
     )
     , "'"
     , ''
    )   
   ) - LENGTH(REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(COLUMN_TYPE, 'enum(', '')
        , ')'
        , ''
     )
     , "'"
     , ''
    ), ',', '')) + 1 AS number_of_items

FROM information_schema.COLUMNS
WHERE
   TABLE_SCHEMA = DATABASE()
 AND
   TABLE_NAME = 'test'
 AND
   COLUMN_NAME = 'department'

看演示

最后一部分是知道如何分隔逗号分隔值..

SELECT 
 SUBSTRING_INDEX(
   SUBSTRING_INDEX(
       'accounts,sales,marketing,customer support,billing,logistics,production'
     , ','
     , 1)
   ,
   ','
   , -1
 )

看演示

当您查看演示时，您会注意到一个递增的数字以获取下一个项目。
现在你知道为什么我需要使用 sql 数字生成器了。

所有魔术（部分）组合

SELECT
  number
 , SUBSTRING_INDEX(
   SUBSTRING_INDEX(comma_separated, ',', number), ',', -1)

FROM (

SELECT 
 (@number := @number + 1) AS number
FROM (    
  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) AS record_1
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

) AS sql_number_generator
CROSS JOIN (

SELECT
 REPLACE(
   REPLACE(
      REPLACE(COLUMN_TYPE, 'enum(', '')
      , ')'
      , ''
   )
   , "'"
   , ''
 ) AS comma_separated
 , LENGTH(
    REPLACE(
      REPLACE(
        REPLACE(COLUMN_TYPE, 'enum(', '')
        , ')'
        , ''
     )
     , "'"
     , ''
    )   
   ) - LENGTH(REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(COLUMN_TYPE, 'enum(', '')
        , ')'
        , ''
     )
     , "'"
     , ''
    ), ',', '')) + 1 AS number_of_items

FROM information_schema.COLUMNS
WHERE
   TABLE_SCHEMA = DATABASE()
 AND
   TABLE_NAME = 'test'
 AND
   COLUMN_NAME = 'department'


) AS a
WHERE
 number BETWEEN 0 AND number_of_items

结果

| number | SUBSTRING_INDEX(
   SUBSTRING_INDEX(comma_separated, ',', number), ',', -1) |
| ------ | --------------------------------------------------------------------------- |
| 1      | accounts                                                                    |
| 2      | sales                                                                       |
| 3      | marketing                                                                   |
| 4      | customer support                                                            |
| 5      | billing                                                                     |
| 6      | logistics                                                                   |
| 7      | production                                                                  |

看演示

MySQL 版本之间的性能可能会有所不同，或者如果 MySQL 版本缓存information_schema.COLUMNS在内存中或存在要访问的优化information_schema..
此外，性能还取决于整个服务器上现有列的数量。

推荐阅读