如何在数据库中存储一年中每一天的价格? [英] How can I store price of something for every day of the year in database?

问题描述

我正在从事一个项目，但是数据库设计存在问题. 我需要存储一年中每一天的价格，但是我需要能够更改特定日期的价格.

I am working on a project but a have a problem with designing of database. I need to store the price of something for every day in the year but i need to be able to change the prices for specific day.

我是否需要每天创建一列，所以我必须在表中创建365列.

Do i need to create a column for every day, in this approach i must create 365 columns in the table.

您对此问题有更好的解决方案吗，谢谢您的帮助.

Do you have any better solution to this problem, any help will be appreciated, thank you.

推荐答案

您应该创建一个包含6列的表.

You should create a table with 6 columns.

CREATE TABLE IF NOT EXISTS priceHistory (
    `price` decimal(8,2) NOT NULL,
    `date` datetime,
    `productId` VARCHAR(50),
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `createdAt` TIMESTAMP DEFAULT NOW(),
    `updatedAt` TIMESTAMP
) ENGINE = INNODB;

现在您可以插入每天的日期和价格，created_at，updatedAt和id列将自动插入(并且updatedAt自动更新)，因此您无需费心还有.

Now you can insert the date and price for every day, the columns created_at, updatedAt and id are automatically inserted (and updatedAt automatically updated), so you don't need to bother for them any more.

如果您要每天保存这些价格并稍后访问数据，则甚至不需要date列，只需使用createdAt，该列又会自动在INSERT上创建.

If you are saving those prices on a daily base and access the data later, you don't even need the date column, just use createdAt which, again, is automatically created on INSERT.

一旦有了数据，就可以像

Once you have data, you can query for it like

SELECT * FROM priceHistory WHERE DATE(`date`) = '2015-02-29';

您可能还会找到 mysql的文档对DATE函数有用.

You might also find mysql's documentation on DATE functions usefull.

编辑

正如@murenik在他的回答中提到的那样，推荐的方法是与包含您的产品详细信息的表建立关系.为此，请将productId语句更改为

As @murenik mentioned in his answer, the recomended way would be to create a relation to the table holding your product details, which you might have. To do this, change the productId statement to

productId INT PRIMARY KEY REFERENCES products(id),

这将链接这些表，使将来的查询更容易，更有效.

This will link those tables, making future queries easier and more effective.

SELECT ph.*, p.* 
FROM products p 
INNER JOIN priceHistory ph on p.id = ph.productId

请参见mysql JOIN .

这篇关于如何在数据库中存储一年中每一天的价格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！