database SQL DataType - 如何存储一年？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/696506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL DataType - How to store a year?
提问by peter
I need to insert a year(eg:1988 ,1990 etc) in a database. When I used Date or Datetime data type, it is showing errors. Which datatype should I use.
我需要在数据库中插入一年（例如：1988、1990 等）。当我使用 Date 或 Datetime 数据类型时，它显示错误。我应该使用哪种数据类型。
采纳答案by TheTXI
If you need to store a year in the database, you would either want to use an Integer datatype (if you are dead set on only storing the year) or a DateTime datatype (which would involve storing a date that basically is 1/1/1990 00:00:00 in format).
如果您需要在数据库中存储一年，您可能想要使用 Integer 数据类型（如果您只存储年份）或 DateTime 数据类型（这将涉及存储基本上为 1/1/ 1990 00:00:00 格式）。
回答by KM.
regular 4 byte INT is way to big, is a waste of space!
常规的 4 字节 INT 太大了，浪费空间！
You don't say what database you're using, so I can't recommend a specific datatype. Everyone is saying "use integer", but most databases store integers at 4 bytes, which is way more than you need. You should use a two byte integer (smallint on SQL Server), which will conserve space better.
你没有说你使用的是什么数据库，所以我不能推荐特定的数据类型。每个人都在说“使用整数”，但大多数数据库以 4 个字节存储整数，这远远超出了您的需要。您应该使用两个字节的整数（SQL Server 上的 smallint），这样可以更好地节省空间。
回答by user786900
Hey,you can Use year()datatype in MySQL It is available in two-digit or four-digit format.
嘿，你可以在 MySQL 中使用year()数据类型它有两位或四位格式。
Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069
注意：四位数格式允许的值：1901 到 2155。两位数格式允许的值：70 到 69，代表从 1970 年到 2069 年的年份
回答by Qubits
Storing a "Year" in MSSQL would ideally depend on what you are doing with it and what the meaning of that "year" would be to your application and database. That being said there are a few things to state here. There is no "DataType" for Year as of 2012 in MSSQL. I would lean toward using SMALLINT as it is only 2 bytes (saving you 2 of the 4 bytes that INT demands). Your limitation is that you can not have a year older than 32767 (as of SQL Server 2008R2). I really do not think SQL will be the database of choice ten thousand years from now let alone 32767. You may consider INT as the Year() function in MSSQL does convert the data type "DATE" to an INT. Like I said, it depends on where you are getting the data and where it is going, but SMALLINT should be just fine. INT would be overkill ... unless you have other reasons like the one I mentioned above or if the code requirements need it in INT form (e.g. integrating with existing application). Most likely SMALLINT should be just fine.
理想情况下，在 MSSQL 中存储“年份”取决于您使用它做什么以及该“年份”对您的应用程序和数据库的意义。话虽如此，这里有几件事要说明。MSSQL 中截至 2012 年没有“数据类型”。我倾向于使用 SMALLINT，因为它只有 2 个字节（节省了 INT 要求的 4 个字节中的 2 个）。您的限制是您的年份不能超过 32767（从 SQL Server 2008R2 开始）。我真的不认为 SQL 会成为一万年后选择的数据库，更不用说 32767。您可以考虑 INT，因为 MSSQL 中的 Year() 函数确实将数据类型“DATE”转换为 INT。就像我说的，这取决于你从哪里获取数据以及它要去哪里，但 SMALLINT 应该没问题。INT会矫枉过正...... 除非您有其他原因，例如我上面提到的原因，或者代码要求需要以 INT 形式（例如与现有应用程序集成）。很可能 SMALLINT 应该没问题。
回答by Frederik Gheysels
Just a year, nothing else ? Why not use a simple integer ?
就一年，没有别的？为什么不使用简单的整数？
回答by Ender
Use integer if all you need to store is the year. You can also use datetime if you think there will be date based calculations while querying this column
如果您需要存储的只是年份，请使用整数。如果您认为在查询此列时会进行基于日期的计算，也可以使用 datetime
回答by Rawheiser
Storage may be only part of the issue. How will this value be used in a query?
存储可能只是问题的一部分。这个值将如何在查询中使用？
Is it going to be compared with another date-time data types, or will all the associated rows also have numeric values?
它是要与另一种日期时间数据类型进行比较，还是所有关联的行也都有数值？
How would you deal with a change to the requirements? How easily could you react to a request to replace the year with a smaller time slice? i.e. Now they want it broken down by quarters?
你会如何处理需求的变化？您对用更小的时间片替换年份的请求做出反应有多容易？即现在他们想要按季度细分？
A numeric type can be easily used in a date time query by having a look-up table to join with containing things like the start and stop dates (1/1/X to 12/31/x), etc..
通过将查找表与包含开始和停止日期（1/1/X 到 12/31/x）等内容的查找表连接，可以轻松地在日期时间查询中使用数字类型。
回答by Saad Ahmad
I don't think using an integer or any subtype of integer is a good choice. Sooner or later you will have to do other date like operations on it. Also in 2019 let's not worry too much about space. See what those saved 2 bytes costed us in 2000.
我不认为使用整数或整数的任何子类型是一个不错的选择。迟早您将不得不对其进行其他日期操作。同样在 2019 年，让我们不要太担心空间。看看那些节省的 2 个字节在 2000 年花费了我们多少。
I suggest use a date of year + 0101 converted to a true date. Similarly if you need to store a month of a year store year + month + 01 as a true date.
我建议使用年份 + 0101 转换为真实日期的日期。同样，如果您需要存储一年中的一个月，则将 year + month + 01 存储为真实日期。
If you have done that you will be able to properly do "date stuff" on it later on
如果你这样做了，你将能够在以后正确地对其进行“约会”
回答by Arslan Ahmad
you are trying to insert a year in the database then use:type YEAR
您正在尝试在数据库中插入一年，然后使用：type YEAR
