mysql - 复杂的mysql插入查询

Question

我正在尝试编写一个迁移脚本(在程序的两个版本之间)来填充 phppos_permissions_actions 表。

填充规则是:“如果用户拥有该模块的权限(基于 phppos_permissions)，那么他们将被授予该模块的所有操作权限。(可以在 phppos_module_actions 中查找)”。

我正在尝试编写一个查询或一组查询来实现以下规则。有人可以引导我走向正确的方向吗？下面是我的架构

mysql> describe phppos_modules;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| name_lang_key | varchar(255) | NO   | UNI | NULL    |       |
| desc_lang_key | varchar(255) | NO   | UNI | NULL    |       |
| sort          | int(10)      | NO   |     | NULL    |       |
| module_id     | varchar(255) | NO   | PRI | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> select * from phppos_modules;
+-------------------+------------------------+------+------------+
| name_lang_key     | desc_lang_key          | sort | module_id  |
+-------------------+------------------------+------+------------+
| module_config     | module_config_desc     |  100 | config     |
| module_customers  | module_customers_desc  |   10 | customers  |
| module_employees  | module_employees_desc  |   80 | employees  |
| module_giftcards  | module_giftcards_desc  |   90 | giftcards  |
| module_item_kits  | module_item_kits_desc  |   30 | item_kits  |
| module_items      | module_items_desc      |   20 | items      |
| module_receivings | module_receivings_desc |   60 | receivings |
| module_reports    | module_reports_desc    |   50 | reports    |
| module_sales      | module_sales_desc      |   70 | sales      |
| module_suppliers  | module_suppliers_desc  |   40 | suppliers  |
+-------------------+------------------------+------+------------+
10 rows in set (0.00 sec)
mysql> describe phppos_modules_actions;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| action_id       | varchar(255) | NO   | PRI | NULL    |       |
| module_id       | varchar(255) | NO   | PRI | NULL    |       |
| action_name_key | varchar(255) | NO   |     | NULL    |       |
| sort            | int(11)      | NO   |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

mysql> select * from phppos_modules_actions;
+----------------+-----------+--------------------------------+------+
| action_id      | module_id | action_name_key                | sort |
+----------------+-----------+--------------------------------+------+
| add_update     | customers | module_action_add_update       |    1 |
| add_update     | employees | module_action_add_update       |  130 |
| add_update     | item_kits | module_action_add_update       |   70 |
| add_update     | items     | module_action_add_update       |   40 |
| add_update     | suppliers | module_action_add_update       |  100 |
| delete         | customers | module_action_delete           |   20 |
| delete         | employees | module_action_delete           |  140 |
| delete         | item_kits | module_action_delete           |   80 |
| delete         | items     | module_action_delete           |   50 |
| delete         | suppliers | module_action_delete           |  110 |
| search         | customers | module_action_search_customers |   30 |
| search         | employees | module_action_search_employees |  150 |
| search         | item_kits | module_action_search_item_kits |   90 |
| search         | items     | module_action_search_items     |   60 |
| search         | suppliers | module_action_search_suppliers |  120 |
| see_cost_price | items     | module_see_cost_price          |   61 |
+----------------+-----------+--------------------------------+------+
16 rows in set (0.00 sec)


mysql> describe phppos_permissions
    -> ;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| module_id | varchar(255) | NO   | PRI | NULL    |       |
| person_id | int(10)      | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from phppos_permissions;
+------------+-----------+
| module_id  | person_id |
+------------+-----------+
| config     |         1 |
| customers  |         1 |
| employees  |         1 |
| giftcards  |         1 |
| item_kits  |         1 |
| items      |         1 |
| receivings |         1 |
| reports    |         1 |
| sales      |         1 |
| suppliers  |         1 |
| sales      |       301 |
| sales      |       741 |
| config     |       759 |
| customers  |       759 |
| employees  |       759 |
| giftcards  |       759 |
| item_kits  |       759 |
| items      |       759 |
| receivings |       759 |
| reports    |       759 |
| sales      |       759 |
| suppliers  |       759 |
| sales      |       776 |
+------------+-----------+
23 rows in set (0.00 sec)


mysql> describe phppos_permissions_actions;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| module_id | varchar(255) | NO   | PRI | NULL    |       |
| person_id | int(11)      | NO   | PRI | NULL    |       |
| action_id | varchar(255) | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

Answer 1

有

insert phppos_permissions_actions (module_id, person_id, action_id)
select distinct 
    phppos_permissions.module_id, phppos_permissions.person_id, action_id
from phppos_permissions
    inner join phppos_modules_actions on phppos_permissions.module_id = phppos_modules_actions.module_id
order by module_id, person_id

解决你的问题了吗？