使行在MySQL中处于非活动状态 [英] Making a row inactive in MySQL

问题描述

是否有可能使MySQL中的行处于非活动状态?因此，该行不再用于查询结果吗? 我的客户希望将删除的成员保留在数据库中，但是我不想编辑所有查询以检查该成员是否被删除.

Is it possible to make a row in MySQL inactive? So this row isn't used in the results of queries anymore? My client wants to keep the deleted members exists in the database, but I don't want to edit all the queries to check if the member is deleted or not.

还是有一种简单的方法将整个行数据移动到另一个非活动"表中?

Or is there an easy way to move the entire row data into another "inactive" table?

推荐答案

您可以重命名当前表，在其中创建已删除"列，然后创建一个与当前表同名的视图，并选择所有删除= 0.这样，您不必更改所有查询.如果您为delete列提供默认值，则该视图将是可更新的. _

You could rename the current table, create the 'deleted' column in it, and then create a view with the same name as the current table, selecting all where deleted=0. That way you don't have to change all your queries. The view will be updateable provided you supply a default for the delete column. _

CREATE TABLE my_new_table (col1    INTEGER,
                           col2    INTEGER,
                           col3    INTEGER,
                           deleted INTEGER NOT NULL DEFAULT 0);

INSERT INTO my_new_table (col1, col2, col3)
    SELECT (col1, col2, col3)
        FROM my_table;

DROP TABLE my_table;

CREATE VIEW my_table (col1, col2, col3)
    AS SELECT (col1, col2, col3)
           FROM my_new_table
           WHERE deleted = 0;

这篇关于使行在MySQL中处于非活动状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！