MySQL is a database that has been bending the SQL standard in ways that make it hard to move off MySQL. What may appear to be a clever technique for vendor lockin (or maybe just oversight of the standard) can be quite annoying in understanding the real meaning of the SQL language. One such example … Continue reading Use MySQL’s Strict Mode on all new Projects! →
Infrequent SQL developers often get confused about when to put parentheses and/or aliases on derived tables. There has been this recent Reddit discussion about the subject, where user Elmhurstlol was wondering why they needed to provide an alias to the derived table (the subselect with the UNION) in the following query: SELECT AVG(price) AS AVG_PRICE … Continue reading Should I Put That Table Alias or Not? →
MySQL... We've blogged about MySQL before. Many times. We've shown bad ideas implemented in MySQL here: MySQL Bad Idea #384 MySQL Bad Idea #573 But this beats everything. Check out this Stack Overflow question. It reads: "Why Oracle does not support 'group by 1,2,3'?". At first, I thought this user might have been confused because … Continue reading MySQL Bad Idea #666 →
The SQL language and its depths... Some of you readers might be aware of MySQL's capability of counting distinct records through the COUNT() aggregate function. The MySQL documentation reads: COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values. In other words, you can count distinct first and last names very easily: SELECT … Continue reading Counting Distinct Records in SQL →
Today at Kaleidoscope I will be doing a 90-minute session comparing MySQL’s SQL syntax to the ANSI/ISO SQL:2003 standard, entitled What Do You Mean, “SQL Syntax Error”?
You can download the PDF slides now.
For those that may be following along the presentation later today (4 pm Eastern time), here are some links that I may throw out during the session:
SQL 2003 standard – actually it is “Information
taken from the Final Committee Draft (FCD) of ISO/IEC
9075-2:2003″ but it’s extremely close to the actual standard.
The actual standard is a document that costs a non-trivial
amount of money to get, and cannot be republished.
MySQL does not follow the ANSI SQL standard for quoting. MySQL’s default quoting behavior is that either single or double quotes can be used to quote a string (this gets me into trouble when I work with Oracle databases, as double quotes do not indicate a string!).
mysql> SELECT 'alive'; +-------+ | alive | +-------+ | alive | +-------+ 1 row in set (0.00 sec) mysql> SELECT "alive"; +-------+ | alive | +-------+ | alive | +-------+ 1 row in set (0.00 sec)
Bare words are dealt with in context; in this case, a bare word would be parsed as a column name:
mysql> SELECT alive; ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
Backquotes are the way MySQL escapes table names. So, if you want
a reserved word, number or operator to be the name of an object
(ie, a table named “1″ or a column named “date”) you need to use
backquotes to avoid a syntax error….for example:
mysql> …[Read more]
By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.
At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.
On Monday, June 28th from 4 pm – 5:30 pm I will be presenting …[Read more]