如果满足某些条件,我想在表(如下所示)获得新插入时自动删除行。
什么时候:
- 有些行引用具有相同"user_id"的相同"字段"
- 它们的"字段"、"显示"和"搜索"列相同
简单地说,当行变得重复时("group_id"列除外),应删除非空的"group_id",否则应更新或插入行。
有没有办法在mysql中设置它(本着"ON DUPLICATE做事"的精神与唯一键等相结合),还是我必须在php中显式检查它(具有多个查询)?
附加信息:对于每个可能的"字段",应该始终有一行带有 NULL "group_id"(有一个有限的集合,在其他地方定义)。另一方面,可能没有一个带有非空"group_id"的。
CREATE TABLE `Views` (
`user_id` SMALLINT(5) UNSIGNED NOT NULL,
`db` ENUM('db_a','db_b') NOT NULL COLLATE 'utf8_swedish_ci',
`field` VARCHAR(40) NOT NULL COLLATE 'utf8_swedish_ci',
`display` TINYINT(1) UNSIGNED NOT NULL,
`search` TINYINT(1) UNSIGNED NOT NULL,
`group_id` SMALLINT(6) UNSIGNED NULL DEFAULT NULL,
UNIQUE INDEX `user_id` (`field`, `db`, `user_id`),
INDEX `Views_ibfk_1` (`user_id`),
INDEX `group_id` (`group_id`),
CONSTRAINT `Views_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `User` (`id`) ON
UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_swedish_ci'
ENGINE=InnoDB;
我认为你需要修改你的逻辑。插入一行只是为了删除另一行是没有意义的。为什么不将重复行中的Group_ID
字段更新为正在插入的内容?以下是我将如何去做的粗略想法。
注:我没有用MySQL做太多工作,也无法让以下内容在SQLFiddle上运行,但根据MySQL文档,我无法找出原因。也许更精通MySQL的人可以纠正我?
SET @User_ID = 1;
SET @db = 'db_a';
SET @Field = 'Field';
SET @Display = 1;
SET @Search = 1;
SET @Group_ID = 1;
IF EXISTS
( SELECT 1
FROM Views
WHERE User_ID = @User_ID
AND DB = @DB
AND Field = @Field
AND Group_ID IS NOT NULL
)
THEN
UPDATE Views
SET Group_ID = @Group_ID,
Display = @Display,
Search = @Search
WHERE User_ID = @User_ID
AND DB = @DB
AND Field = @Field
AND Group_ID IS NOT NULL
ELSE
INSERT INTO Views (User_ID, DB, Field, Display, Search, Group_ID)
VALUES (@User_ID, @DB, @Field, @Display, @Search, @Group_ID)
END IF;
或者(也是我的首选解决方案),将时间戳字段添加到表中并创建一个视图,如下所示:
SELECT v.User_ID, v.DB, v.Field, v.Display, v.Search, v.Group_ID
FROM Views v
INNER JOIN
( SELECT User_ID, DB, Field, MAX(CreatedDate) AS CreatedDate
FROM Views
WHERE Group_ID IS NOT NULL
GROUP BY User_ID, DB, Field
) MaxView
ON MaxView.User_ID = v.User_ID
AND MaxView.DB = v.DB
AND MaxView.Field = v.Field
AND MaxView.CreatedDate = v.CreatedDate
WHERE v.Group_ID IS NOT NULL
UNION ALL
SELECT v.User_ID, v.DB, v.Field, v.Display, v.Search, v.Group_ID
FROM Views v
WHERE v.Group_ID IS NULL
这将允许您正确跟踪对数据的更改,而不会影响能够查看唯一记录的需求。
从视图中删除group_id,其中group_id!='NUll'
你的问题不是很好理解,所以我不确定这是你想要的:
DELETE FROM Views WHERE # delete from the table views
group_id IS NOT NULL AND # first condition delete only rows with not null group_id
(SELECT count(*) as tot FROM Views GROUP BY group_id) = 1 # second condition count the difference in group id
如果这不是您想要的,请更新您的问题以提供更多详细信息...