问题描述
我想在MySQL数据仓库中处理日期维度。 (我在DW世界的新手)
我用google做了一些搜索,看到很多表结构(大部分)日期维度,主键是一个简单的 UNSIGNED INTEGER
。
为什么不使用 DATE
字段作为主键,因为MySQL是3字节VS 4字节 INTEGER
?
Ex:
CREATE TABLE dimDate
id INTEGER UNSIGNED NOT NULL PRIMARY AUTOI_NCREMENT,
日期DATE NOT NULL,
dayOfWeek
...
VS
CREATE TABLE dimDate
pre>
日期DATE NOT NULL PRIMARY,
dayOfWeek
...
解决方案如果您有一个列为
日期的表
类型,哪里没有两行将有相同的日期,那么您可以肯定地将此列用作PRIMARY KEY
。
你看到很多例子,主键是简单的
UNSIGNED INTEGER
,因为有许多情况下,主键没有完美的候选人。AUTO_INCREMENT
允许此列在插入期间由数据库自动填充(并且是唯一的)。I want to handle a date dimension in a MySQL datawarehouse. (I m a newbie in the DW world)
I made some searches with google and saw a lot of table structures (most of) date dimension where the Primary Key is a simple
UNSIGNED INTEGER
.Why don't use a
DATE
field as primary key since with MySQL it is 3 Bytes VS 4 Bytes forINTEGER
?Ex:
CREATE TABLE dimDate id INTEGER UNSIGNED NOT NULL PRIMARY AUTOI_NCREMENT, date DATE NOT NULL, dayOfWeek ...
VS
CREATE TABLE dimDate date DATE NOT NULL PRIMARY, dayOfWeek ...
解决方案If you have a table with a column that is of
date
type and where no two rows will ever have the same date, then you can surely use this column asPRIMARY KEY
.You see a lot of examples where the Primary Key is a simple
UNSIGNED INTEGER
because there are many cases where there is no perfect candidate for Primary Key. TheAUTO_INCREMENT
allows this column to be automatically filled by the database (and be unique) during inserts .这篇关于使用DATE字段作为MySQL的日期维度的主键的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!