mysql串聯刪除操作

標籤：engine   mysql   

mysql> CREATE TABLE `roottb` (

    ->   `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,

    ->   `data` VARCHAR(100) NOT NULL DEFAULT ‘‘,

    ->   PRIMARY KEY (`id`)

    -> ) engine=InnoDB;

Query OK, 0 rows affected (0.60 sec)

mysql> CREATE TABLE `subtb` (

    ->   `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,

    ->   `rootid` INT(11) UNSIGNED NOT NULL DEFAULT ‘0‘,

    ->   `data` VARCHAR(100) NOT NULL DEFAULT ‘‘,

    ->   PRIMARY KEY (`id`),

    ->   INDEX (`rootid`),

    ->   FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE

    -> ) engine=InnoDB;

Query OK, 0 rows affected (0.16 sec)

mysql> INSERT INTO `roottb` (`id`,`data`)

    ->   VALUES (‘1‘, ‘test root line 1‘),

    ->          (‘2‘, ‘test root line 2‘),

    ->          (‘3‘, ‘test root line 3‘);

Query OK, 3 rows affected (0.08 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql>

mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`)

    ->   VALUES (‘1‘, ‘1‘, ‘test sub line 1 for root 1‘),

    ->          (‘2‘, ‘1‘, ‘test sub line 2 for root 1‘),

    ->          (‘3‘, ‘1‘, ‘test sub line 3 for root 1‘),

    ->          (‘4‘, ‘2‘, ‘test sub line 1 for root 2‘),

    ->          (‘5‘, ‘2‘, ‘test sub line 2 for root 2‘),

    ->          (‘6‘, ‘2‘, ‘test sub line 3 for root 2‘),

    ->          (‘7‘, ‘3‘, ‘test sub line 1 for root 3‘),

    ->          (‘8‘, ‘3‘, ‘test sub line 2 for root 3‘),

    ->          (‘9‘, ‘3‘, ‘test sub line 3 for root 3‘);

Query OK, 9 rows affected (0.14 sec)

Records: 9  Duplicates: 0  Warnings: 0

mysql> delete from `roottb` where `id`=‘2‘;

Query OK, 1 row affected (0.16 sec)

mysql> select * from `roottb`;

+----+------------------+

| id | data             |

+----+------------------+

|  1 | test root line 1 |

|  3 | test root line 3 |

+----+------------------+

2 rows in set (0.02 sec)

mysql> select * from `subtb`;

+----+--------+----------------------------+

| id | rootid | data                       |

+----+--------+----------------------------+

|  1 |      1 | test sub line 1 for root 1 |

|  2 |      1 | test sub line 2 for root 1 |

|  3 |      1 | test sub line 3 for root 1 |

|  7 |      3 | test sub line 1 for root 3 |

|  8 |      3 | test sub line 2 for root 3 |

|  9 |      3 | test sub line 3 for root 3 |

+----+--------+----------------------------+

6 rows in set (0.00 sec)

本文出自 “Linux營運” 部落格，請務必保留此出處http://2853725.blog.51cto.com/2843725/1561019

mysql串聯刪除操作