如何在 MySQL 过程中获取 ENUM 的值

发布于 2024-11-02 17:45:04 字数 598 浏览 5 评论 0原文

在我看来，枚举在 MySQL 中被破坏了，至少在版本 5.0.51a-24+lenny5 (Debian) 上是这样。我有一个简单的查找表，其中包含一个枚举字段：

CREATE TABLE `FrameworkServices` (
  ...
  `DNSType` enum('IP','CNAME') NOT NULL,
  ...
)

临时，我可以毫无问题地运行以下命令：

SELECT DNSType, CNameOrIP INTO @hackEnum, @ipAddress
FROM FrameworkServices WHERE FrameworkTypeId = 1 LIMIT 1;
SELECT @hackEnum, @ipAddress;

但是，如果我将完全相同的代码放入过程中，@hackEnum 返回空（不为空，至少通过 TOAD）。

我尝试选择 DNSType+0、CONCAT(DNSType,'') 以及其他似乎对枚举有效的内容，结果是相同的：我阅读的每一篇文章都是临时的，但在程序中返回空！

我什至尝试使用局部变量并将 hackEnum 定义为相同的 ENUM 类型。

原文

It seems to me that enums are broken within MySQL, at least on version 5.0.51a-24+lenny5 (Debian). I have a simple lookup table which contains an enum field:

CREATE TABLE `FrameworkServices` (
  ...
  `DNSType` enum('IP','CNAME') NOT NULL,
  ...
)

Ad-hoc, I can run the following without issue:

SELECT DNSType, CNameOrIP INTO @hackEnum, @ipAddress
FROM FrameworkServices WHERE FrameworkTypeId = 1 LIMIT 1;
SELECT @hackEnum, @ipAddress;

However, if I put the EXACT same code into a Procedure, @hackEnum returns empty (not null, at least through TOAD).

I have tried selecting DNSType+0, CONCAT(DNSType,'') and everything else that seems to be valid on enums and the result is the same:
EVERY article I read works ad-hoc, but returns empty within the Procedure!

I've even tried using local variables and defining hackEnum as the same ENUM type.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

无人问我粥可暖 2024-11-09 17:45:05

我遇到了完全相同的问题 - 我正在检索 ENUM 值，然后通过 IF...ELSE...END IF 检查它。它不起作用，让我抓狂，不知道为什么。解决方案是选择一个 char(1) ，然后将 ENUM 列值视为字符串：

DECLARE use_free CHAR(1);

SELECT use_free INTO use_free FROM ...;

IF use_free = '1' THEN
  <do something>
ELSE
  <do something else>
END IF;

我首先使用：

DECLARE use_free ENUM('0','1');

虽然技术上是正确的，但失败了。希望这对您有帮助。

I had the exact same problem - I was retrieving an ENUM value and then checking it via IF...ELSE...END IF. It wasn't working and driving me nuts as to why not. The solution was to select into a char(1) and then treat the ENUM column value as a string:

DECLARE use_free CHAR(1);

SELECT use_free INTO use_free FROM ...;

IF use_free = '1' THEN
  <do something>
ELSE
  <do something else>
END IF;

I first used:

DECLARE use_free ENUM('0','1');

and while technically correct, failed. Hope this helps you.

回复收藏 0 原文

浅唱ヾ落雨殇 2024-11-09 17:45:05

只声明你的返回变量，有 varchar(xx);

CREATE TABLE `FrameworkServices` (
  ...
  `DNSType` enum('IP','CNAME') NOT NULL,
  ...
)

declare valor varchar(40);
select DNSType from FrameworkServices into valor;

这一切！

Only declare your variable of return, has varchar(xx);

CREATE TABLE `FrameworkServices` (
  ...
  `DNSType` enum('IP','CNAME') NOT NULL,
  ...
)

declare valor varchar(40);
select DNSType from FrameworkServices into valor;

and this all!!

回复收藏 0 原文

~没有更多了~