我知道这个问题已经很久了,但是它在这些年里得到了很多关注,我认为它缺少一个概念,这个概念可能会帮助到类似情况的人们。出于完整性考虑,在此处添加它。
如果您无法修改原始数据库架构,则已提供很多好的答案,并且可以很好地解决问题。
然而,如果您可以修改架构,我建议在您的“customer”表中添加一个字段,用于保存此客户的最新“customer_data”记录的“id”。
CREATE TABLE customer (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
current_data_id INT UNSIGNED NULL DEFAULT NULL
);
CREATE TABLE customer_data (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id INT UNSIGNED NOT NULL,
title VARCHAR(10) NOT NULL,
forename VARCHAR(10) NOT NULL,
surname VARCHAR(10) NOT NULL
);
查询客户
查询操作非常简单快捷:
SELECT c.*, d.title, d.forename, d.surname
FROM customer c
INNER JOIN customer_data d on d.id = c.current_data_id
WHERE ...;
缺点是在创建或更新客户时会增加额外的复杂性。
更新客户
每当您想要更新客户信息时,您需要在customer_data
表中插入一条新记录,并更新customer
记录。
INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(2, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = 2;
创建客户
创建客户只需要插入customer
条目,然后运行相同的语句:
INSERT INTO customer () VALUES ();
SET @customer_id = LAST_INSERT_ID();
INSERT INTO customer_data (customer_id, title, forename, surname) VALUES(@customer_id, 'Mr', 'John', 'Smith');
UPDATE customer SET current_data_id = LAST_INSERT_ID() WHERE id = @customer_id;
总结
创建/更新客户的额外复杂性可能会让人望而却步,但可以使用触发器轻松自动化此过程。
最后,如果您正在使用ORM,这将非常容易管理。ORM可以负责插入值、更新ID并自动为您连接两个表。
以下是可变的Customer
模型示例:
class Customer
{
private int id;
private CustomerData currentData;
public Customer(String title, String forename, String surname)
{
this.update(title, forename, surname);
}
public void update(String title, String forename, String surname)
{
this.currentData = new CustomerData(this, title, forename, surname);
}
public String getTitle()
{
return this.currentData.getTitle();
}
public String getForename()
{
return this.currentData.getForename();
}
public String getSurname()
{
return this.currentData.getSurname();
}
}
您不可变的CustomerData
模型,该模型仅包含getter:
class CustomerData
{
private int id;
private Customer customer;
private String title;
private String forename;
private String surname;
public CustomerData(Customer customer, String title, String forename, String surname)
{
this.customer = customer;
this.title = title;
this.forename = forename;
this.surname = surname;
}
public String getTitle()
{
return this.title;
}
public String getForename()
{
return this.forename;
}
public String getSurname()
{
return this.surname;
}
}