Question

如果符合某些条件,在表格(如下表所示)获得新插入时,我要自动删除行。

何时:

There are rows referring to the same field with the same user_id
Their field , display and search columns are the same

简而言之,当行将成为重复的行时(组代列除外),非无号组代号应予删除,否则应更新或插入一行。

是否有办法在 Mysql (以“ ON DUPLIATE < em> do stuff stuff < / em> 和独有的密钥等 ” 的精神) 中设置此功能, 或者我是否必须在php( 包含多个查询) 中明确检查?

Additional info: There should always be a row with NULL group_id for every possible field (there s a limited set, defined elsewhere). On the other hand there might not be one with a non null 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;

Answer 1

我认为您需要修改您的逻辑。插入一行只是为了删除另一行是没有道理的。为什么不将重复行中的 < code> Group_ ID 字段更新到插入的内容? 下面是对于我如何处理它的一个粗略的想法。

N.b.。我对 MySQL 没有做很多工作, 我无法在下面运行SQFiddle , 但基于MySQL docs 我无法解决原因。也许在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

这样可以正确跟踪数据的变化,同时不影响查看独特记录的必要性。

Answer 2

删除 Group_ id 所在的视图中的 group_ id 组。= NUll

Answer 3

你的问题不是很好理解, 所以我不确定这是你想要的:

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

如果这不是你想要的,请以更多细节更新你的问题...

友情链接