'Data Types' Tag

  • Re: MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation

    January 24, 2013

    Reading Sheeri's MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation, I was baffled at this change of floating point number notation. However, I was also concerned about the final action taken: using "--ignore-columns" to avoid comparing the FLOAT/DOUBLE types. The --float-precision option for pt-table-checksum currently only uses ROUND() so as to disregard minor […]

  • CHAR and spaces

    June 12, 2012

    I know about it, I knew about it all along, but... it's so easy to fall for it; there's just so much absurdity! A CHAR type has a known number of characters. For example, the column: CountryCode CHAR(3) CHARSET ascii NOT NULL - is known to have exactly three characters. These could be 'USA', 'FRA', […]

  • 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.) […]

Powered by Wordpress and MySQL. Theme by openark.org