如何删除数据库中的多条记录

有两种方法可以做到这一点：

1. 从所有 3 个表中手动删除。下面是对此的查询：

   delete prsn, addr, cv from person prsn join address addr on p.person_address = addr.address_id join cv cv on prsn.person_cv = cv.cv_id where prsn.id = ?

2. 创建外键约束，以便在删除父记录后自动删除子记录。 例如，像这样重新创建地址表：

   create table address( ... FOREIGN KEY (address_id) REFERENCES person(person_Address) ON DELETE CASCADE );

然后，我们只需要执行以下查询：

delete from person where id = ?

我从屏幕截图中看到您已经在使用InnoDB表，因此使用person_id作为引用列在address和cv表上设置一些外键依赖项应该不会太难。

作为使用外键实现似乎导致问题的rippled delete的示例，这里有三个设置了外键依赖项的表 - 这样您就可以从person表中删除~使用人员的id，相关表中的所有记录也将被删除。

create table if not exists `person` (
  `id` int(10) unsigned not null auto_increment,
  `name` varchar(50) not null,
  primary key (`id`)
) engine=innodb auto_increment=2 default charset=latin1;
create table if not exists `address` (
  `id` int(10) unsigned not null auto_increment,
  `person_id` int(10) unsigned not null default '0',
  `address` varchar(50) not null default '0',
  primary key (`id`),
  unique key `person_id` (`person_id`),
  constraint `fk_user_address` foreign key (`person_id`) references `person` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=2 default charset=latin1;
create table if not exists `cv` (
  `id` int(10) unsigned not null auto_increment,
  `person_id` int(10) unsigned not null default '0',
  `cv` varchar(50) not null default '0',
  primary key (`id`),
  key `person_id` (`person_id`),
  constraint `fk_user_cv` foreign key (`person_id`) references `person` (`id`) on delete cascade on update cascade
) engine=innodb auto_increment=2 default charset=latin1;
/* dummy data */
insert into `person` (`id`, `name`) values (1, 'bobby');
insert into `address` (`id`, `person_id`, `address`) values (1, 1, '99 riverside drive, dundee');
insert into `cv` (`id`, `person_id`, `cv`) values (1, 1, 'bobby_cv.docx');

/* this is how the tables look */    
person table
+----+-------+
| id | name  |
+----+-------+
|  1 | Bobby |
+----+-------+
address table
+----+---------------+----------------------------+
| id | person_id(fk) | address                    |
+----+---------------+----------------------------+
|  1 |         1     | 99 Riverside Drive, Dundee |
+----+---------------+----------------------------+
cv table
+----+---------------+---------------+
| id | person_id(fk) | cv            |
+----+---------------+---------------+
|  1 |         1     | bobby_cv.docx |
+----+---------------+---------------+

在这种情况下，要删除与 Bobby 关联的所有记录，sql 可以简单地是：

delete from `person` where `id`=1;

如果不使用这样的外键，则需要为要从中删除用户及其关联记录的每个表构造单独的 sql 语句。

即：

delete from person where person_id=X;
delete from address where person_id=X;
delete from cv where person_id=X;

更新

对延迟响应表示歉意 - 睡眠电话。

在用于显示用户和其他表中的关联详细信息的 sql SELECT 语句中，您不选择person_id，这是使删除语句工作所需的键（双关语）元素。

$sql = "SELECT person_id, person_firstname, person_lastname, 
       person_email, person_phonenumber,  
       address_street,address_housenumber, 
       address_city,address_state,address_zipcode, cv_path
FROM person 
   inner join address on address.address_id = person.person_address 
   inner join cv on cv.cv_id = person.person_cv";

那么，在while循环中

while($row = $result->fetch_assoc()) {
    $person_id=$row['person_id'];
    /* etc - other content as before */
    /* note the parameter used is "ID" */
    echo "<a href='delete.php?id={$person_id}'>Delete</a>";
}

然后在delete.php前提是设置了 FK 依赖项（这完全取决于您如何完成这些）

/* options used to ensure supplied id is within acceptable bounds */
$options = array(
    'options' => array(
        'default' => 0,
        'min_range' =>  1,
        'max_range' =>  10000000 /* maximum value for ID */
    )
);
/* process GET parameters - notably "ID" */
$id=filter_input( INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT );
$id=(int)filter_var( $id, FILTER_VALIDATE_INT, $options );
/* construct & execute sql */
if( is_int( $id ) ){
    $sql="delete from `person` where `id`='{$id}';";
    $result=mysql_query( $sql );
}

一个没有解决的问题是SQL注入。原始代码和我在这里详细介绍的内容都使用现已弃用的mysql_*函数系列，并且容易受到 sql 注入的影响。一旦您解决了此删除问题，明智的做法是迁移代码以使用mysqli以便您可以利用prepared statements