mysql - 数据库多对多关系完整性更新

Question

数据库有order和customer表，以及order_customer多对多表。为简单起见，假设orderhasid和customer_id（后一个字段对于多对多关系不需要，但由于历史原因保留）字段和hasorder_customer和id字段。此外，所有表都有字段（是的，它是 laravel）——如果该行没有被删除，则为 NULL。 我做了一些完整性检查，以了解是否需要旧字段。 order_idcustomer_iddeleted_at
customer_id

    select `order`.`id`, `order`.`customer_id`
    from `order`
    where
    `order`.customer_id not in (
    select customer_id from order_customer
    where `order_customer`.deleted_at is null
    #and `order_customer`.order_id = `order`.`order_id`
    )
    and
    `order`.deleted_at is null;

返回 25 行。所以我只是order.customer_id在帐户中添加了该字段。
但现在我被要求修复order和order_customer表中的所有完整性问题。所以我做了一个简单的程序python来检查所有 3 种情况：

1. order.custmer_id不存在于select customer_id from order_customer where order_id = order.id
2. order_customer.order_id不存在于select id from order
3. order.id不存在于select order_id from order_customer

import mysql.connector

config = {
  'user': 'root',
  'password': '***',
  'host': '127.0.0.1',
  'database': '***',
  'raise_on_warnings': True
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
query = ("select `order`.`id`, `order`.`customer_id` "
         "from `order` "
         "where `order`.deleted_at is null"
         )

cursor.execute(query)

orders=list(cursor)

query = ("select `order_customer`.`order_id`,`order_customer`.`customer_id` "
         "from `order_customer` "
         "where `order_customer`.deleted_at is null"
         )

cursor.execute(query)

m2m=list(cursor)

cursor.close()
cnx.close()

print len(set(c for i,c in orders)-set(c for o,c in m2m))

25
>>> from collections import defaultdict
>>> d=defaultdict(set)
>>> for o,c in m2m:
    d[o].add(c)

>>> len(d)
40983
>>> len(set(i for i,c in orders if i in d and c not in d[i]))
37
>>> s=set(i for i,c in orders);x=[(o,c) for o,c in m2m if o not in s]
>>> len(x)
1377
>>> x[:10]
[(15968, 2), (98, 3), (109, 3), (115, 3), (1199, 3), (1200, 3), (3626, 3), (3843, 3), (4303, 3), (4681, 3)]
>>> max(o for o,c in x)
37653
>>> len(m2m)
42122
>>> len(orders)
39628

但我需要这些东西SQL。

主要部分-->

select `order`.id,`order`.customer_id,
(select 
`order_customer`.customer_id
from `order_customer` where
`order_customer`.order_id = `order`.id) as customer_ids
from `order`
where
customer_id not in customer_ids;

根本不起作用，但我需要类似的东西来包含在insert/update查询中。谢谢。

标签： mysqlsql