gpt4 book ai didi

mysql更新插入连接避免重复

转载 作者:行者123 更新时间:2023-11-29 14:34:47 25 4
gpt4 key购买 nike

我有一个表想要更新(或者如果先前条件不存在则插入)

servers table              clusters table              datacenters table  

server_id | server_name cluster_id | cluster_name datacenter_id | datacenter_name
----------------------- ------------------------- -------------------------------
1 server1 1 cluster1 1 datacentereast
2 server7 3 Cluster22 5 datacenterwest


server_status table

status_id | status_server | status_cluster | status_datacenter
---------------------------------------------------------------------

我希望使用其他表的 id 填充 server_status 并避免重复。

到目前为止我已经:

sql_string = """  
INSERT into server_status (status_datacenter,status_cluster, status_server)
SELECT
d.datacenter_id,c.cluster_id,s.server_id
FROM
datacenters as d, clusters as c, servers as s
WHERE
d.datacenter_name = \'%s\'
AND c.cluster_name = \'%s\'
AND s.server_name = \'%s\'
AND d.datacenter_id != status_datacenter
AND c.cluster_id != status_cluster
AND s.server_id != status_server;
""" % (datacenter,cluster,server)

以上失败并显示:

_mysql_exceptions.OperationalError: (1054, "Unknown column 'status_datacenter' in 'where clause'")

打印出实际的sql命令:

INSERT into server_status (status_datacenter,status_cluster, status_server) 
SELECT d.datacenter_id,c.cluster_id,s.server_id
FROM datacenters as d, clusters as c, servers as s
WHERE d.datacenter_name = 'SDDC'
AND c.cluster_name = 'qboc37'
AND s.server_name = 'ap30'
AND d.datacenter_id != status_datacenter
AND c.cluster_id != status_cluster
AND s.server_id != status_server;

最佳答案

首先在server_status (status_datacenter,status_cluster, status_server)上创建唯一索引,然后

INSERT IGNORE into server_status (status_datacenter,status_cluster, status_server) 
SELECT d.datacenter_id,c.cluster_id,s.server_id
FROM datacenters as d, clusters as c, servers as s
WHERE d.datacenter_name = 'SDDC'
AND c.cluster_name = 'qboc37'
AND s.server_name = 'ap30'

关于mysql更新插入连接避免重复,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/9299243/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com