Find the Best Approach for Entering Dates in MySQL Databases
A function as straightforward as entering dates in a MySQL database should be nearly automatic, but the process is anything but foolproof. MySQL’s handling of invalid date entries can leave developers scratching their heads. In particular, the globalization of IT means you’re never sure where the server hosting your database will be located — or relocated. Plan ahead to ensure your database’s date entries are as accurate as possible.
DBAs know that if they want their databases to function properly, they have to follow the rules. The first problem is, some "rules" are more like guidelines, allowing a great deal of flexibility in their application. The second problem is, it’s not always easy to determine which rules are rigid, and which are more malleable.
An example of a rule with some built-in wiggle room is MySQL’s date handling. Database Journal’s Rob Gravelle explains in a September 8, 2014, post that MySQL automatically converts numbers and strings into a correct Date whenever you add or update data in a DATE, DATETIME, or TIMESTAMP column. The string has to be in the "yyyy-mm-dd" format, but you can use any punctuation to separate the three date elements, such as "yyyy&;mm&;dd", or you can skip the separators altogether, as in "yyyymmdd".
So what happens when a Date record has an invalid entry, or no entry at all? MySQL inserts its special zero date of "0000-00-00" and warns you that it has encountered an invalid date, as shown below.
Only the first of the four Date records is valid, so MySQL warns that there is an invalid date after entering the zero date of "0000-00-00". Source: Database Journal
To prevent the zero date from being entered, you can use NO_ZERO_DATE in strict mode, which generates an error whenever an invalid date is entered; or NO_ZERO_IN_DATE mode, which allows no month or day entry when a valid year is entered. Note that both of these modes have been deprecated in MySQL 5.7.4 and rolled into strict SQL mode.
Other options are to enable ALLOW_INVALID_DATES mode, which permits an application to store the year, month, and date in three separate fields, for example, or to enable TRADITIONAL SQL Mode, which acts more like stricter database servers by combining STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_AUTO_CREATE_USER.
Avoid using DATETIME at all? Not quite
Developer Eli Billauer posits on his personal blog that it is always a mistake to use the MySQL (and SQL) DATETIME column type. He qualifies his initial blanket pronouncement to acknowledge that commenters to the post give examples of instances where use of DATETIME is the best approach.
Billauer points out that many developers use DATETIME to store the time of events, as in this example:
Using the DATETIME and NOW() functions creates problems because you can’t be sure of the local server’s time, or the user’s timezone. Source: Eli Billauer
Because DATETIME relies on the time of the local server, you can’t be sure where the web server hosting the app is going to be located. One way around this uncertainty is to apply a SQL function that converts timezones, but this doesn’t address such issues as daylight savings time and databases relocated to new servers. (Note that the UTC_TIMESTAMP() function provides the UTC time.)
Troubleshooting and monitoring the performance of your MySQL, MongoDB, Redis, and ElasticSearch databases is a piece of cake when you use theMorpheus database-as-a-service (DBaaS). Morpheus provides a single, easy-to-use dashboard. In addition to a free full replica set of each database instance, you get automatic daily backups of your MySQL and Redis databases.
Morpheus is the first and only DBaaS to support SQL, NoSQL, and in-memory databases. The service’s SSD-backed infrastructure ensures peak performance, and direct links to EC2 guarantee ultra-low latency. Visit the Morpheus site to create a free account .
Axure汉化版已经发布，版本号Axure 126.96.36.1999，下面是截图效果 Axure汉化版文件下载地址：Axure汉化补丁 Axure RP pro 188.8.131.529 下载地址注册用户名：Axure 序列...
BI中文站 6月7日报道 艾默生·斯帕茨(Emerson Spartz)今年28岁，已婚，是Spartz Inc公司的首席执行官。Spartz Inc是一个网站媒体帝国，旗下的30多家网站专门发布有趣、励志和让人感到不可思议的文章和帖子，其分享量非常...
- mysql 将字段time按天/月/年分组
- 新安装mysql 第三方工具连接不上问题
- CentOS 安装MySQL 5.1.69
- mysql出现“Incorrect key file for table”解决办法
- mysql无法启动——cannot allocate the memory for the buffer pool
- Mysql 日志删除
- mysql 修改字段类型 删除字段类型
- 修改mysql字符编码出现Job failed to start解决办法
- Why TokuDB hates Transparent HugePages