mysql中只加入了一行_MySQL只加入最近一行？

我知道这个问题已经过时了，但多年来它引起了很多关注，我认为它缺少一个概念，可以帮助某个类似案例的人 . 为了完整起见，我在这里添加它 .

如果您无法修改原始数据库架构，那么已经提供了很多好的答案并且很好地解决了问题 .

但是，如果您可以修改架构，我建议在 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;

}

}