http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570  에서 참조

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won’t actually be inserted if it results in a duplicate key. But the statement won’t generate an error. It generates a warning instead. These cases include:

  • Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
  • Inserting a NULL into a column with a NOT NULL constraint.
  • Inserting a row to a partitioned table, but the values you insert don’t map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

  • A new auto-increment ID is allocated.
  • Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
  • Triggers that fire on DELETE are executed unnecessarily.
  • Side effects are propagated to replication slaves too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.


http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update/548570#548570  에서 참조

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won’t actually be inserted if it results in a duplicate key. But the statement won’t generate an error. It generates a warning instead. These cases include:

  • Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
  • Inserting a NULL into a column with a NOT NULL constraint.
  • Inserting a row to a partitioned table, but the values you insert don’t map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

  • A new auto-increment ID is allocated.
  • Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
  • Triggers that fire on DELETE are executed unnecessarily.
  • Side effects are propagated to replication slaves too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.


http://www.mysqldiary.com/user-defined-variables/ 에서 참조

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is much more to explore.

By using User-defined variables, one can add some kind of procedural logic to the MySQL’s relational logic. They are connection specific variables, that means that they persist as long as the connection to the database persists and are specific to a connection (the variable value is not accessible via different connections). User variables can be assigned a value with a SET or SELECT statements and are accessible written as @var_name.

Before we dive into the fascinating uses and examples I would like to point some cautions with MySQL User Defined Variables:

  • Case sensitivity: Currently they are case sensitive (they are case insensitive at older versions >5.0).
  • They prevent query cache.
  • If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.
  • The order of user defined evaluation is undefined.

So lets see some examples:

Example 1:
The first example is the simplest one. We would like to numerate the MySQL query result. Lets say we have a table called users and we would like to numerate some result from it:

SELECT

    @counter:=@counter+1 as counter,

    users.*

FROM

    (SELECT @counter:=0) v,

    users;

Example 2:
How can you produce only the 100th rows? Can you do it in a pure relational logic? I don’t think so. However, this is possible and easy to do with the User defined variables:
Lets say we have a table called users.

SELECT

    users.*

FROM

    (SELECT @counter:=0) v, 

    users

HAVING

    (@counter:=@counter+1)%100=0

ORDER BY

    user_id;

This query will print only the 100th users from that table. The statement (select @counter:=0) initialize the @counter variable to zero. This is not the only way to do it, we could wrote a prior statement to set the variable to zero:

SET @counter:=0;

SELECT

    users.*

FROM

    users

HAVING

    (@counter:=@counter+1)%100=0

ORDER BY

    user_id;

The statement having (@counter:=@counter+1)%2=0 has two operation: the first is to increment the variable and the second is to return true value for the 100th records.

Example 3:

It is quit simple to produce an accumulative totals with User-defined variable:

Say we have a table of bank account transactions by year and month.  To produce a bank account transaction report by months we can use:

SELECT

   t.year,

   t.month,

   @x:=@x+t.c

FROM

   (SELECT @x:=0) a,

   (SELECT

        year,month,sum(amount) AS c

    FROM

        account_transactions

    GROUP BY year,month) t

Example 4:

Numerate the MySQL query result per user (restarts the numbering every time user_id changes)

SELECT

   user_id,

   user_time,

   user_total_value,

   @x:=IF(@same_value=user_id,@x+1,1) as numerate,

   @same_value:=user_id as dummy

FROM

   users,

   (SELECT  @x:=0, @same_value:='') t

ORDER BY user_id,user_time;

Accumulative totals per user (restarts the numbering every time user_id changes)

SELECT

   user_id,

   user_time,

   user_total_value,

   @x:=IF(@same_value=user_id,@x+user_total_value,1) as numerate,

   @same_value:=user_id as dummy

FROM

   users,

   (SELECT  @x:=0, @same_value:='') t

ORDER BY user_id,user_time;

That is it for now.
I will be glad to receive more interesting examples that uses MySQL’s User Defined variables.

Tip: If you liked this post I am recommending reading also the following great post: Advanced MySQL user variable techniques

http://www.mysqldiary.com/user-defined-variables/ 에서 참조

When I have discovered MySQL User-defined variables at the first time, I didn’t pay much attention to them and didn’t realize their strength. At first, one can think that they are just one of the many unnoticed MySQL features. Well, I was wrong. Here, I will try to summarize their strength, though I think there is much more to explore.

By using User-defined variables, one can add some kind of procedural logic to the MySQL’s relational logic. They are connection specific variables, that means that they persist as long as the connection to the database persists and are specific to a connection (the variable value is not accessible via different connections). User variables can be assigned a value with a SET or SELECT statements and are accessible written as @var_name.

Before we dive into the fascinating uses and examples I would like to point some cautions with MySQL User Defined Variables:

  • Case sensitivity: Currently they are case sensitive (they are case insensitive at older versions >5.0).
  • They prevent query cache.
  • If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.
  • The order of user defined evaluation is undefined.

So lets see some examples:

Example 1:
The first example is the simplest one. We would like to numerate the MySQL query result. Lets say we have a table called users and we would like to numerate some result from it:

SELECT

    @counter:=@counter+1 as counter,

    users.*

FROM

    (SELECT @counter:=0) v,

    users;

Example 2:
How can you produce only the 100th rows? Can you do it in a pure relational logic? I don’t think so. However, this is possible and easy to do with the User defined variables:
Lets say we have a table called users.

SELECT

    users.*

FROM

    (SELECT @counter:=0) v, 

    users

HAVING

    (@counter:=@counter+1)%100=0

ORDER BY

    user_id;

This query will print only the 100th users from that table. The statement (select @counter:=0) initialize the @counter variable to zero. This is not the only way to do it, we could wrote a prior statement to set the variable to zero:

SET @counter:=0;

SELECT

    users.*

FROM

    users

HAVING

    (@counter:=@counter+1)%100=0

ORDER BY

    user_id;

The statement having (@counter:=@counter+1)%2=0 has two operation: the first is to increment the variable and the second is to return true value for the 100th records.

Example 3:

It is quit simple to produce an accumulative totals with User-defined variable:

Say we have a table of bank account transactions by year and month.  To produce a bank account transaction report by months we can use:

SELECT

   t.year,

   t.month,

   @x:=@x+t.c

FROM

   (SELECT @x:=0) a,

   (SELECT

        year,month,sum(amount) AS c

    FROM

        account_transactions

    GROUP BY year,month) t

Example 4:

Numerate the MySQL query result per user (restarts the numbering every time user_id changes)

SELECT

   user_id,

   user_time,

   user_total_value,

   @x:=IF(@same_value=user_id,@x+1,1) as numerate,

   @same_value:=user_id as dummy

FROM

   users,

   (SELECT  @x:=0, @same_value:='') t

ORDER BY user_id,user_time;

Accumulative totals per user (restarts the numbering every time user_id changes)

SELECT

   user_id,

   user_time,

   user_total_value,

   @x:=IF(@same_value=user_id,@x+user_total_value,1) as numerate,

   @same_value:=user_id as dummy

FROM

   users,

   (SELECT  @x:=0, @same_value:='') t

ORDER BY user_id,user_time;

That is it for now.
I will be glad to receive more interesting examples that uses MySQL’s User Defined variables.

Tip: If you liked this post I am recommending reading also the following great post: Advanced MySQL user variable techniques

13.2.10.7. Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

Notice that the subquery contains a reference to a column of t1, even though the subquery’s FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because (5,6) is not equal to (5,7)), so the expression as a whole is FALSE.

Scoping rule: MySQL evaluates from inside to outside. For example:

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ... renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that is farther out.

For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list.

For certain cases, a correlated subquery is optimized. For example:

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.

Aggregate functions in correlated subqueries may contain outer references, provided the function contains nothing but outer references, and provided the function is not contained in another function or expression.