'Data Types' Tag

  • 23:59:59 is not the end of the day. No, really!

    July 27, 2011

    How would you check whether some TIMESTAMP column falls within a given day, say July 26th, 2011? This is a question I tend to ask students, and usually I get the same range of answers. Some are wrong; some cannot utilize an index, some are correct, and some are temporarily correct. I wish to take [...]

  • Announcing common_schema: common views & routines for MySQL

    July 13, 2011

    Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server. What does it do? There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are [...]

  • TIMESTAMP vs. DATETIME, which should I be using?

    May 22, 2011

    They both look the same, have roughly the same use. Nevertheless, they differ in many respects. I'll note the difference, and note a few pitfalls and peculiarities. Range TIMESTAMP starts with the epoch, '1970-01-01 00:00:01' UTC and ends with '2038-01-19 03:14:07' UTC. This is all very nice today, and may actually hold up till our [...]

  • Implicit casting you don't want to see around

    July 7, 2010

    In Beware of implicit casting, I have outlined the dangers of implicit casting. Here's a few more real-world examples I have tackled: Number-String comparisons Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus: The second query consists of pure strings comparison. It [...]

  • Choosing MySQL boolean data types

    June 3, 2010

    How do you implement True/False columns? There are many ways to do it, each with its own pros and cons. ENUM Create you column as ENUM('F', 'T'), or ENUM('N','Y') or ENUM('0', '1'). This is the method used in the mysql tables (e.g. mysql.user privileges table). It's very simple and intuitive. It truly restricts the values [...]

  • But I DO want MySQL to say "ERROR"!

    March 12, 2010

    MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data. Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though). Calling CREATE TEMPORARY TABLE? You get silent commit. Issuing a ROLLBACK on non-transactional involved [...]

  • Useful temporal functions & queries

    December 8, 2009

    Here's a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations. There are many ways to solve such problems. I'll present my favorites. Querying for time difference Given two timestamps: ts1 (older) and ts2 (newer), how much time has [...]

  • Character sets: latin1 vs. ascii

    July 8, 2009

    Unless specified otherwise, latin1 is the default character set in MySQL. What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.) [...]

  • The depth of an index: primer

    April 9, 2009

    InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index). In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves. Assuming the index is not in memory, the depth [...]

  • MySQL's character sets and collations demystified

    December 8, 2008

    MySQL's character sets and collations are often considered as a mystery, and many users either completely disregard them and keep with the defaults, or set everything to UTF8.

    This post will attempt to shed some light on the mystery, and provide with some best practices for use with text columns with regard to character sets.

 
Powered by Wordpress and MySQL. Theme by openark.org