当某些列 = 0 时动态删除记录；数据清理

发布于 2024-10-12 02:59:41 字数 801 浏览 1 评论 0原文

我有一个简单的卡片表：

CREATE TABLE `users_individual_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `individual_card_id` int(11) NOT NULL,
  `own` int(10) unsigned NOT NULL,
  `want` int(10) unsigned NOT NULL,
  `trade` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`individual_card_id`),
  KEY `user_id_2` (`user_id`),
  KEY `individual_card_id` (`individual_card_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

我有 ajax 来根据 OWN、WANT 和 TRADE 添加和删除记录。但是，如果用户删除所有“拥有”、“想要”和“交易”卡，它们会变为零，但会在数据库中保留记录。我宁愿删除该记录。每次“更新”后检查是否所有列 = 0 是执行此操作的唯一方法吗？或者我可以设置一个条件触发器，例如：

//psuedo sql
AFTER update IF (OWN = 0, WANT = 0, TRADE = 0) DELETE

执行此操作的最佳方法是什么？你能帮忙解决一下语法吗？

原文

I have a simple card table:

CREATE TABLE `users_individual_cards` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `individual_card_id` int(11) NOT NULL,
  `own` int(10) unsigned NOT NULL,
  `want` int(10) unsigned NOT NULL,
  `trade` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`individual_card_id`),
  KEY `user_id_2` (`user_id`),
  KEY `individual_card_id` (`individual_card_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

I have ajax to add and remove the records based on OWN, WANT, and TRADE. However, if the user removes all of the OWN, WANT, and TRADE cards, they go to zero but it will leave the record in the database. I would prefer to have the record removed. Is checking after each "update" to see if all the columns = 0 the only way to do this? Or can I set a conditional trigger with something like:

//psuedo sql
AFTER update IF (OWN = 0, WANT = 0, TRADE = 0) DELETE

What is the best way to do this? Can you help with the syntax?

分享到QQ

分享到微博

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

发布评论

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

七婞 2024-10-19 02:59:41

为什么不直接从 PHP（或其他前端）触发两个查询？

update `users_individual_cards` ...
delete `users_individual_cards` where ... (same condition) and own + want + trade = 0

Why not just fire two queries from PHP (or other front end)?

update `users_individual_cards` ...
delete `users_individual_cards` where ... (same condition) and own + want + trade = 0

回复收藏 0 原文

尽揽少女心 2024-10-19 02:59:41

触发器将是：

CREATE TRIGGER users_individual_cards_trigger
    AFTER UPDATE ON users_individual_cards
    FOR EACH ROW
    BEGIN
    DELETE FROM users_individual_cards
    WHERE 'OWN' = 0 AND 'WANT' = 0 AND 'TRADE' = 0;
    END$

抛出删除查询的解决方案会更好，因为并非所有版本的 mysql 都支持它。

The trigger will be:

CREATE TRIGGER users_individual_cards_trigger
    AFTER UPDATE ON users_individual_cards
    FOR EACH ROW
    BEGIN
    DELETE FROM users_individual_cards
    WHERE 'OWN' = 0 AND 'WANT' = 0 AND 'TRADE' = 0;
    END$

The solutions throw the delete query will be better because not all versions of mysql support it.

回复收藏 0 原文

~没有更多了~