# MySQL高级

## 设置索引
```sql
ALTER TABLE router ADD INDEX idx_id_parent_id (id, parent_id);
ALTER TABLE router ADD INDEX idx_id (id);
ALTER TABLE router ADD INDEX idx_parent_id (parent_id);

SHOW INDEX FROM router;

ALTER TABLE router DROP INDEX id;
ALTER TABLE router DROP INDEX parent_id;
```

## 递归查询
```sql
WITH RECURSIVE my_table AS (
  SELECT
  *
  FROM
  router
  WHERE
  parent_id = 0 UNION ALL
  SELECT
  ung.*
  FROM
  router ung
  INNER JOIN my_table ON ung.parent_id = my_table.id
) SELECT
*
FROM
my_table
```

**递归查询id直到查询到父级id为0**

```sql
WITH RECURSIVE ParentChain AS (
  SELECT *
  FROM router
  WHERE id IN (1833698026257588226,1833698026257588228)
  UNION ALL
  SELECT r.*
  FROM router r
  INNER JOIN ParentChain pc ON  r.id = pc.parent_id
)
SELECT DISTINCT * FROM ParentChain;
```

