何时使用VARCHAR和DATE / DATETIME [英] When to use VARCHAR and DATE/DATETIME

查看:95
本文介绍了何时使用VARCHAR和DATE / DATETIME的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

所以我们在Freenode上进行了这个编程讨论,当我试图使用VARCHAR(255)来存储一个日期变量来存储这个格式的D / MM / YYYY时,就出现了这个问题。所以问题是为什么使用VARCHAR存储日期是如此糟糕。她的优点是:


  1. 代码速度更快。以前,我使用了DATE,但是日期格式化是一个真正的痛苦。

  2. 使用字符串比Date更强大的功能?谁在乎,我们住在Ghz时代。

  3. 它不符合伦理正确(lolwut?)这是另一个用户告诉我的...



那么你最喜欢用什么来存储日期? SQL VARCHAR或SQL DATE?

解决方案

当您拥有超过2-3万行的数据库时,您会知道为什么最好使用DATETIME而不是VARCHAR:)



简单的回答是,使用数据库 - 处理能力不再是问题了。只是数据库的大小是因为HDD的寻道时间。



基本上使用现代硬盘,您可以读取约100条记录/秒,如果它们以随机顺序读取(通常情况下),那么您必须尽可能地减少DB大小,因为:




  • 硬盘的头不必旅行这么多

  • 您将在RAM中更多的数据



最后,硬盘的追求时间将会杀死你。例如。一些简单的GROUP BY查询与许多行可能需要几个小时在磁盘上完成相比,几秒钟完成在RAM =>因为寻找时间。



VARCHAR您无法进行任何搜索。如果您讨厌SQL如何处理日期太多,只需在32位整数字段中使用unix时间戳。您将(基本上)使用SQL DATE字段的所有优点,您只需使用选择的编程语言(而不是SQL函数)操作和格式化日期。


so we had this programming discussion on Freenode and this question came up when i was trying to use a VARCHAR(255) to store a Date Variable in this format: D/MM/YYYY. So the Question is why is it so bad to use VARCHAR to store date. Her are the advantages:

  1. Its faster to code. Previously i used DATE, but date formatting was a real pain.
  2. Its more power hungry to use string than Date? Who cares, we live in the Ghz era.
  3. Its not ethically correct (lolwut?) This is what the other user told me...

So what would you prefer to use to store a date? SQL VARCHAR or SQL DATE?

解决方案

When you'll have database with more than 2-3 million rows you'll know why it's better to use DATETIME than VARCHAR :)

Simple answer is that with databases - processing power isn't a problem anymore. Just the database size is because of HDD's seek time.

Basically with modern harddisks you can read about 100 records / second if they're read in random order (usually the case) so you must do everything you can to minimize DB size, because:

  • The HDD's heads won't have to "travel" this much
  • You'll fit more data in RAM

In the end it's always HDD's seek times that will kill you. Eg. some simple GROUP BY query with many rows could take a couple of hours when done on disk compared to couple of seconds when done in RAM => because of seek times.

For VARCHAR's you can't do any searches. If you hate the way how SQL deals with dates so much, just use unix timestamp in 32 bit integer field. You'll have (basically) all advantages of using SQL DATE field, you'll just have to manipulate and format dates using your choosen programming language, not SQL functions.

这篇关于何时使用VARCHAR和DATE / DATETIME的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆