'Syntax' Tag

  • Merging tables with INSERT...ON DUPLICATE KEY UPDATE

    February 21, 2013

    Had a case recently where I had to merge data from two identically structured tables containing nearly identical data. "Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one […]

  • Pop quiz: funny syntax

    November 5, 2012

    The following questions are of little importance, yet I find them entertaining. I stumbled upon these while developing QueryScript. Can you guess the results of the following statements? Pop quiz 1 SET @x := 7; SELECT ++@x; What is the computation result? What will be the value of @x? Pop quiz 2 SET @ := […]

  • MySQL joins: ON vs. USING vs. Theta-style

    July 11, 2012

    What is the difference between the following three syntaxes? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id The difference is mostly syntactic sugar, but with a couple interesting notes. To put names, the first two are […]

  • Syntax of the day: IS TRUE and IS FALSE

    January 26, 2012

    What makes for a true statement? We usually test statements using a WHERE clause: SELECT * FROM world.City WHERE Population > 1000000 The "Population > 1000000" statement makes for a boolean expression. Using WHERE is just one way of evaluating it. One can also test with IF(): SET @val := 7; SELECT IF(@val > 2, […]

  • Quoting text JavaScript/Python style

    November 15, 2011

    Unless your MySQL is configured to use ANSI_QUOTES in sql_mode, you are able to quote your text in one of two forms: using single quotes or double quotes: UPDATE world.Country SET HeadOfState = 'Willy Wonka' WHERE Code='USA' UPDATE world.Country SET HeadOfState = "Willy Wonka" WHERE Code="USA" This makes for JavaScript- or Python-style quoting: you quote […]

  • MySQL terminology: processes, threads & connections

    November 3, 2010

    There's some confusion in the MySQL terminology for processes, threads & connections, which I will try to resolve. I can't explain the full nature of what processes and threads are; please see Wikipedia [1] [2] for that. But here's some basics with regard to MySQL: MySQL server is a single process application. It is multithreaded. […]

  • SQL: good comments conventions

    July 1, 2010

    I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code. I list four major places where SQL comments are helpful. I'll use the sakila database. It is originally scarcely commented; I'll present it now enhanced with comments, to illustrate. Table definitions The CREATE TABLE statement […]

  • Views: better performance with condition pushdown

    May 20, 2010

    Justin's A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer's site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with […]

  • Discovery of the day: GROUP BY ... DESC

    May 4, 2010

    I happened on a query where, by mistake, an was written as And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It's documented. In MySQL, GROUP BY results are sorted according to the group statement. You can override this by adding ORDER BY […]

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

 
Powered by Wordpress and MySQL. Theme by openark.org