mysql逻辑删除的问题_数据库表涉及-逻辑删除

对于只进行逻辑删除的表，如

CREATE TABLE `pics` (

`pid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`aid` bigint(20) unsigned NOT NULL,

`pic_url` varchar(255) NOT NULL,

`is_cover` tinyint(1) default 0,

`is_local` tinyint(1) default 1,

`type` int(10) unsigned default 0,

`description` text,

`ext` text,

`uid` bigint(20) unsigned NOT NULL,

`uip` int(10) unsigned default 0,

`create_time` int(10) unsigned default 0,

`update_time` int(10) unsigned default 0,

`is_audited` tinyint(1) default 0,

`is_deleted` tinyint(1) default 0,

PRIMARY KEY (`pid`),

KEY `aid` (`aid`,`create_time`),

KEY `idx_user_time_del` (`uid`,`create_time`,`is_deleted`),

KEY `idx_user_uptime_del` (`uid`,`update_time`,`is_deleted`),

KEY `idx_time_del` (`create_time`,`is_deleted`),

KEY `idx_uptime_del` (`update_time`,`is_deleted`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建议

pics表中只存储未删除的数据，不需要is_delete字段

另建一张表存储删除的数据，如pic_d

用户删除的话就把记录从pics中删除，插入到pic_d中，myisam表引擎即可，好处不言而喻

CREATE TABLE `pics_d` (

`pid` bigint(20) ,

`aid` bigint(20) unsigned NOT NULL,

`pic_url` varchar(255) NOT NULL,

`is_cover` tinyint(1) default 0,

`is_local` tinyint(1) default 1,

`type` int(10) unsigned default 0,

`description` text,

`ext` text,

`uid` bigint(20) unsigned NOT NULL,

`uip` int(10) unsigned default 0,

`create_time` int(10) unsigned default 0,

`update_time` int(10) unsigned default 0,

`is_audited` tinyint(1) default 0,

`is_deleted` tinyint(1) default 0

) ENGINE=Myisam DEFAULT CHARSET=utf8;