'Syntax' Tag

• Proper SQL table alias use conventions

  March 11, 2010

  After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query. I'm going to leave formatting, upper/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I [...]

  posted in MySQL by shlomi

• 7 ways to convince MySQL to use the right index

  April 2, 2009

  Sometimes MySQL gets it wrong. It doesn't use the right index. It happens that MySQL generates a query plan which is really bad (EXPLAIN says it's going to explore some 10,000,000 rows), when another plan (soon to show how was generated) says: "Sure, I can do that with 100 rows using a key". A true [...]

  posted in MySQL by shlomi

• `;`.`*`.`.` is a valid column name

  February 12, 2009

  And the following query: SELECT `;`.`*`.`.` FROM `;`.`*`; is valid as well. So are the following: DROP DATABASE IF EXISTS `;`; CREATE DATABASE `;`; CREATE TABLE `;`.`*` (`.` INT); CREATE TABLE `;`.```` (`.` INT); CREATE TABLE `;`.`$(ls)` (`.` INT);

  posted in MySQL by shlomi

• REPLACE INTO: think twice

  December 17, 2008

  The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation. Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO's. [...]

  posted in MySQL by shlomi

• 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.

  posted in MySQL by shlomi

• Dynamic sequencing with a single query

  December 3, 2008

  It is a known trick to use a session variable for dynamically counting/sequencing rows. The way to go is to SET a variable to zero, then use arithmetic within assignment to increment its value for each row in the SELECTed rows.

  But can it be achieved with one query only? That's more of a problem... I'll provide such a solution, albeit not a pretty one.

  posted in MySQL by shlomi

• Selecting a specific non aggregated column data in GROUP BY

  December 1, 2008

  In a GROUP BY query, MySQL may allow specifying non aggregated columns. For example, using MySQL's world database, I can get the number of countries per continent, along with a "sample" country.
  What if I want to choose that "sample" country? For example, for each continent, I wish to show the country with the largest population. To simply see the largest population, I would use MAX(Population). But which country is referred? I wish to provide a solution which does not involve sub-queries, HAVING or JOINs.

  posted in MySQL by shlomi

• Useful database analysis queries with INFORMATION_SCHEMA

  November 26, 2008

  A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.

  I will present queries for:

  * Checking on database engines and size
  * Locating duplicate and redundant indexes
  * Checking on character sets for columns and tables, looking for variances
  * Checking on processes and long queries

  posted in MySQL by shlomi

• Less known SQL syntax and functions in MySQL

  November 23, 2008

  "Standard SQL" is something you read about. All popular databases have modified version of SQL. Each database adds its own flavor and features to the standard. MySQL is no different.

  Some deviations are storage engine dependent. Others are more general. Many, such as INSERT IGNORE, are commonly used. Here's a list of some MySQL deviations to SQL, which are not so well known.

  posted in MySQL by shlomi