ORACLE & MariaDB SQL Comparison No.1 v1.0

ORACLE & MariaDB Data Manipulation Statements Comparison No.1 v1.0

 

Date	Ver	Etc.
13.06.12	1.0

 

 

1.    SQL Comparison Overview

 

		ORACLE	MariaDB
SQL	UNION ALL, UNION	지원	지원
	ROLLUP	GROUP BY ROLLUP(…)	GROUP BY … WITH ROLL UP
	부분범위처리 (페이징)	ROWNUM	LIMIT
	출력저장	SPOOL	INTO
DELETE	ERROR 에 대한 처리	비지원	IGNORE
INSERT	DEFAULT 값 없는 NOT NULL 컬럼의 INSERT VALUE 누락	허용하지 않음	허용 (strict mode 가 아닐 때)
	values 에 column 사용	허용하지 않음	허용
HINT	조인순서를 강제함	ordered, leading	straight_join
	INDEX	index, no_index	use index (index_list)
BIND VARIABLE		지원 ( :variable_name )	지원 ( @variable_name )

 

2.    Comparison List

l  SELECT syntax

l  Join Syntax

l  Delete syntax

l  Insert syntax

l  Replace syntax

l  UPDATE syntax

l  Declaring Variables

 

여기서는 위 대상에 대해 진행하였다.

 

3.    SELECT syntax

SELECT 구문은 ORACLE 과 동일하다.

다만 구문과 같이 사용되는 옵션이 다르다.

 

ALL | DISTINCT | DISTINCTROW

 

위 옵션들은 중복되는 값에 대한 처리를 컨트롤한다.

ALL 은 default 옵션으로 중복되는 값을 전부 리턴한다.

 

DISTINCT 는 ORACLE 과 마찬가지 기능을 한다.

DISTINCTROW 는 DISTINCT 의 SYNONYM 이다.

 

HIGH_PRIORITY

 

SELECT HIGH_PRIORITY …

 

SELECT 에 UPDATE 보다 더 높은 권한을 부여한다. 테이블에 대해 UPDATE 를 위해 대기중인 UPDATE 와

데이터를 읽기 위한 SELECT HIGH_PRIORITY 가 있을 때 SELECT 가 우선 수행된다.

이 옵션은 테이블 레벨 락킹에만 적용된다. (영향받는 스토리지엔진 : MyISAM, MEMORY, MERGE)

 

STRAIGHT_JOIN

 

FROM 절에 나열된 테이블을 순차적으로 엑세스 하도록 강제한다.

ORACLE HINT – ORDERED 와 동일한 기능을 한다.

 

SQL_CACHE | SQL_NO_CACHE | SQL_CALC_FOUND_ROWS

 

SQL_CACHE 와 SQL_NO_CACHE 는 SQL 수행 결과를 캐싱 할 지 컨트롤한다.

 

이 값은 query_cache_type variable 에 영향을 받으며 default 값은 1이다.

이 경우 SELECT SQL_NO_CACHE … 를 제외하고 캐싱 할 수 있는 모든 값을 캐싱한다.

 

SQL_CALC_FOUND_ROWS MariaDB 에서 지원하나?

 

WITH ROLLUP

 

ORACLE 의 ROLLUP 과 동일하다.

 

MariaDB   MariaDB [test]> select id2, sum(id1) from test3 group by id2 with rollup; +------+----------+ | id2  | sum(id1) | +------+----------+ |    2 |        2 | |    3 |        1 | | NULL |        3 | +------+----------+ 3 rows in set (0.00 sec)     ORACLE   18:53:50 TEST2@TEST2>select id2, sum(id1) from test3 group by rollup(id2);          ID2   SUM(ID1) ---------- ----------          2          2          3          1                     3   Elapsed: 00:00:00.00

 

 

LIMIT

 

결과를 제한한다.

페이징할 때 주로 사용된다.

오라클의 ROWNUM 역할을 하지만 사용은 더 간단하다.

 

SELECT … FROM … WHERE … GROUP … ORDER … HAVING … LIMIT 순서로 오며,

LIMIT {[offset,] row_count | row_count OFFSET offset}] 로 사용된다.

 

보통 LIMIT rowcount, LIMIT offset row_count 형태로 많이 사용하며,

인자가 하나만 쓰인 겨우 몇 개를 리턴할 것인가의 의미이며,

인자가 두개 쓰인 경우는 ‘offset’ 번째의 결과에서 ‘row_count’ 개수만큼 결과를 리턴하란 의미이다.

 

LIMIT 3,2 는 4번째 결과부터 2건을 출력하란 의미가 된다.

offset 의 position 은 0부터 시작으로 0은 첫번째 값을 의미한다.

 

 

MariaDB [test]> select * from test1; +-----+ | id1 | +-----+ |   1 | |   2 | |   3 | |   4 | |   5 | +-----+ 5 rows in set (0.00 sec)   MariaDB [test]> select * from test1 limit 2,2; +-----+ | id1 | +-----+ |   3 | |   4 | +-----+ 2 rows in set (0.00 sec)

 

 

PROCEDURE

 

SELECT … FROM … WHERE … GROUP … ORDER … HAVING … LIMIT … PROCEDURE procedure_name

SELECT ~ LIMIT 에서 얻어진 결과셋을 재처리하기 위해 PROCEDURE 를 호출한다.

 

INTO

 

SELECT … FROM … WHERE … GROUP … ORDER … HAVING … LIMIT … INTO {var_list | OUTFILE | DUMPFILE}

SELECT INTO {var_list | OUTFILE | DUMPFILE} … FROM … WHERE … GROUP … ORDER … HAVING … LIMIT …

 

 

위와 같은 형태로 쓰인다.

INTO 는 결과셋을 어떻게 로컬 파일시스템에 처리(저장) 할 지에 대한 절이며 개략적인 정보는 다음과 같다.

 

l  var_list : variable 에 저장

l  OUTFILE : OUTFILE ‘file_name’ 형태로 쓰며 결과를 파일 시스템에 저장한다.

더불어 사용자가 컬럼 구분자와 row terminator 를 지정할 수 있다.

가장 많이 사용하는 기능이다.

l  DUMPFILE : 파일에 아무 포맷팅 없이 하나의 ROW 를 저장한다.

 

INTO 는 기본적으로 로컬 파일 시스템에 대해 사용가능하며 원격지에 결과를 내려야 하는 경우는 mysql client 가 있는 상황에서

mysql –e “SELECT … “ > file_name

방법으로 저장할 수 있다.

 

INTO OUTFILE [CHARACTER SET charset_name] 형태로도 사용가능하다.

이는 저장할 파일의 캐릭터셋을 지정하며, 생략시 binary character set 으로 지정된다.

 

n  outfile1.txt 쿼리원문   select id1, id2 into outfile ‘/oracle/log/maria/outfile1.txt’ from test3;   n  outfile2.txt 쿼리원문 select id1, id2 into outfile ‘/oracle/log/maria/outfile2.txt’ fields terminated by ‘/’ optionally enclosed by ‘”’ lines terminated by ‘\n’ from test3;   devaspdb@/oracle/log/maria>cat outfile1.txt 1       2 1       3 1       2 devaspdb@/oracle/log/maria>cat outfile2.txt 1/2 1/3 1/2

 

 

FOR UPDATE | LOCK IN SHARE MODE

 

SELECT … FROM … WHERE … GROUP … ORDER … HAVING … LIMIT … FOR UPDATE | LOCK IN SHARE MODE

 

Page 나 row locks 을 사용하는 storage engine 과 FOR UPDATE 를 사용하면, 쿼리에 의해 처리되는 rows 는

write locked 를 Transaction 의 종료까지 획득한다.

 

LOCK IN SHARE MODE 는 쿼리에 의해 처리된 rows 에 대해 shared lock 을 획득하고 다른 트랜잭션이

읽을 수는 있지만 UPDATE 나 DELETE 하지 못한다.

 

4.    Join Syntax

 

table_references:     escaped_table_reference [, escaped_table_reference] ...   escaped_table_reference:     table_reference   | { OJ table_reference }   table_reference:     table_factor   | join_table   table_factor:     tbl_name [[AS] alias] [index_hint_list]   | table_subquery [AS] alias   | ( table_references )   join_table:     table_reference [INNER | CROSS] JOIN table_factor [join_condition]   | table_reference STRAIGHT_JOIN table_factor   | table_reference STRAIGHT_JOIN table_factor ON conditional_expr   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor   join_condition:     ON conditional_expr   | USING (column_list)   index_hint_list:     index_hint [, index_hint] ...   index_hint:     USE {INDEX|KEY}       [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])   | IGNORE {INDEX|KEY}       [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)   | FORCE {INDEX|KEY}       [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)   index_list:     index_name [, index_name] ...

Ref.B 에서 발췌

 

l  index_hint_list

 

FROM (table_name) (table_alias) USE | IGNORE | FORCE {INDEX | KEY} (index_list) 형태로 사용한다.

 

USE 는 해당 인덱스를 사용하라는 정도이며, FORCE 는 사용을 강제한다.

단 possible keys 에 한해서 강제할 수 있다.

IGNORE 는 해당 키를 사용하지 말라는 뜻인나 강제하는지는 알 수 없다.  /*+ 테스트로 확인 */

 

MariaDB [test]> explain     -> select t1.id1, t3.id2     -> from test1 t1 straight_join test3 t3     -> on t1.id1 = t3.id1; +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+ | id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                           | +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+ |    1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using index                                     | |    1 | SIMPLE      | t3    | ALL   | test3_ix2     | NULL    | NULL    | NULL |    3 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+-------+---------------+---------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec)   MariaDB [test]> explain     -> select t1.id1, t3.id2     -> from test1 t1 straight_join test3 t3 force index (test3_ix2)     -> on t1.id1 = t3.id1; +------+-------------+-------+-------+---------------+-----------+---------+-------------+------+-------------+ | id   | select_type | table | type  | possible_keys | key       | key_len | ref         | rows | Extra       | +------+-------------+-------+-------+---------------+-----------+---------+-------------+------+-------------+ |    1 | SIMPLE      | t1    | index | PRIMARY       | PRIMARY   | 4       | NULL        |    5 | Using index | |    1 | SIMPLE      | t3    | ref   | test3_ix2     | test3_ix2 | 5       | test.t1.id1 |    1 |             | +------+-------------+-------+-------+---------------+-----------+---------+-------------+------+-------------+ 2 rows in set (0.00 sec)

 

l  INNER | CROSS JOIN

MySQL 에서 INNER JOIN, CROSS JOIN, JOIN 모두 같다.

 

l  STRAIGHT_JOIN

Join 의 테이블의 엑세스 순서를 고정한다. (A) STRAIGHT_JOIN (B) 와 같이 기술하면,

(A)  를 먼저 엑세스 한 다음 (B) 를 엑세스 한다.

위와 같이 FROM 절에 기술하는 방법이 있고, 또 SELECT STRAIGHT_JOIN … 으로 기술하는 방법이 있다.

FROM 절에 기술한 경우는 STRAIGHT_JOIN 의 대상이 되는 두 테이블에 한정적이지만,

SELECT 다음에 사용한 경우 FROM 절에 순서대로 엑세스 한다.

 

l  {LEFT |RIGHT } [OUTER] JOIN

일반적으로 알고 있는 ANSI 의 LEFT OUTER JOIN, RIGHT OUTER JOIN 이다.

 

l  NATURAL [{LEFT|RIGHT} [OUTER]] JOIN

equi-join (Inner join, join) 의 특수한 케이스이다.

NATURAL JOIN 을 쓰는 경우 ON, USING 을 사용하지 못한다.

 

NATURAL JOIN 의 두 대상 테이블에 대해 공통의 컬럼을 조인컬럼으로 사용하게 된다.

공통 컬럼이 없는 경우 카티션 곱이 일어난다.

 

l  USING (column_list)

조인에 사용할 컬럼이 모든 테이블에 존재해야 하며, ON 과 함께 사용하지 못한다.

 

사용은 다음과 같이 하며 모두 같은 결과를 반환한다.

n  select test1.id1 from test1 join test2 on test1.id1 = test2.id1;

n  select test1.id1 from test1 join test2 using (id1);

 

5.    DELETE Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name     [WHERE where_condition]     [ORDER BY ...]     [LIMIT row_count] Multiple-table syntax:   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]     tbl_name[.*] [, tbl_name[.*]] ...     FROM table_references     [WHERE where_condition] Or:   DELETE [LOW_PRIORITY] [QUICK] [IGNORE]     FROM tbl_name[.*] [, tbl_name[.*]] ...     USING table_references     [WHERE where_condition]

 

ORACLE 과 대비되는 DELETE Syntax 는 LOW_PRIORITY, QUICK, IGNORE 이다.

 

n  LOW_PRIORITY : TABLE-LOCKING 을 쓰는 Storage Engine 에서 적용된다.

 이 옵션을 사용하는 경우 타겟이 되는 테이블을 읽으려는 세션이 없을 때까지 수행이 미뤄진다.

n  QUICK : MyISM 에서만 사용가능한 옵션으로 DELETE 와중에 INDEX LEAVES 를 MERGE 하지 않는다.

이 옵션을 사용하여 delete 수행을 빠르게 할 수도 있다.

n  IGNORE : Deleting rows 과정중에 발생하는 모든 에러를 무시한다. (파싱단계에 발생하는 에러도 무시 됨)

IGNORE 옵션의 사용으로 무시되는 error 는 warning 으로 반환된다.

 

위 옵션을 제외하고 기본적으로 ORACLE 과 동일하게 사용되지만, 예외적인 부분으로

DELETE 의 타겟이 되는 테이블의 서브쿼리에 타겟 테이블을 또 지정하게 되면 DELETE 는 실패한다.

 

다시 말하면 DELETE FROM A WHERE A.B = (SELECT B FROM A …) 는 실패한다.

간단한 테스트로 보면 다음과 같다.

 

MariaDB [test]> delete from test1 where id1 = (select max(id1) from test1); ERROR 1093 (HY000): You can't specify target table 'test1' for update in FROM clause   17:22:43 TEST2@TEST2>delete from test1 where id1 = (select max(id1) from test1);   1 row deleted.

 

DELETE … FROM … LIMIT row_count 의 사용은 Replication Master-Slave 간에 동기화가 깨질 위험,

혹은 데이터가 달라질 위험성이 있어 사용이 권장되지 않는다. 흔히 ORDER BY 와 같이 사용하는 케이스가

있다.

 

6.    INSERT Syntax

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]     [INTO] tbl_name [(col_name,...)]     {VALUES | VALUE} ({expr | DEFAULT},...),(...),...     [ ON DUPLICATE KEY UPDATE       col_name=expr         [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]     [INTO] tbl_name     SET col_name={expr | DEFAULT}, ...     [ ON DUPLICATE KEY UPDATE       col_name=expr         [, col_name=expr] ... ] Or: INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]     [INTO] tbl_name [(col_name,...)]     SELECT ...     [ ON DUPLICATE KEY UPDATE       col_name=expr         [, col_name=expr] ... ]

 

ORACLE 과 차이나는 MariaDB 의 INSERT 동작은 다음과 같다.

 

Strict SQL mode 에서 수행하지 않는 경우 컬럼에 대해 주어지지 않은 값은 그 컬럼의 default 값을

가져간다.

 

MariaDB [test]> insert into test6(id1) values(4); Query OK, 1 row affected, 1 warning (0.01 sec)   MariaDB [test]> show warnings; +---------+------+------------------------------------------+ | Level   | Code | Message                                  | +---------+------+------------------------------------------+ | Warning | 1364 | Field 'id2' doesn't have a default value | +---------+------+------------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from test6; +------+-----+ | id1  | id2 | +------+-----+ |    4 |   0 | +------+-----+ 1 row in set (0.00 sec)

 

위 결과를 보면 id2 에 대해 not null 제약조건이 있고 insert 시 이 컬럼에 대한 값을 넣지 않았다.

그럼에도 불구하고 insert 를 성공했다. 다만 이 과정에서 Warning 이 발생했고 id2 에 대해 default value 가 없다는

이야기를 하고 있다.

 

test6 를 조회해 본 결과 사용자가 의도하지 않은 id2=0 이란 값이 들어가 있다.

int data type 에 대해 MariaDB 가 할당하는 default value 가 0 이라는 걸 알 수 있다.

 

sql_mode 를 strict 로 변경하고 위 INSERT Operation 을 다시 수행하면 다음과 같이 에러를 얻는다.

 

MariaDB [test]> show variables like '%sql_mode%'; +---------------+-------------------+ | Variable_name | Value             | +---------------+-------------------+ | sql_mode      | STRICT_ALL_TABLES | +---------------+-------------------+ 1 row in set (0.00 sec)   MariaDB [test]> insert into test6(id1) values(4); ERROR 1364 (HY000): Field 'id2' doesn't have a default value

 

참고로 sql_mode 의 defulat 값은 ‘ ‘  이며, 이는 default 값이 없는 not null 제약 조건 컬럼에 대해

INSERT 시 누락되더라도 해당 오퍼레이션을 성공한다.

 

또 MariaDB 에서는 insert 의 value 항목에 column 을 사용할 수 있다.

오라클은 지원하지 않는다.

 

MariaDB [test]> insert into test3 values (3, id1*2); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> select * from test3; +------+------+ | id1  | id2  | +------+------+ |    1 |    2 | |    1 |    3 | |    1 |    2 | |    2 |    3 | |    3 |    6 | +------+------+ 5 rows in set (0.00 sec)   MariaDB [test]> insert into test3 values (id2*3, 3); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> select * from test3; +------+------+ | id1  | id2  | +------+------+ |    1 |    2 | |    1 |    3 | |    1 |    2 | |    2 |    3 | |    3 |    6 | | NULL |    3 | +------+------+

 

위 결과의 두 번째 INSERT 구문을 보면 values 에 (id2 * 3, 3) 과 같이 사용했다.

첫 번째와 달리 컬럼이 앞선 케이스고 Ref.D 에서는

“But the following is not legal, because the value for col1 refers to col2, which is assigned after col1”

이라는 이야기를 하고 있다.

첫 번째 컬럼에 대해 컬럼을 사용했는데 해당하는 컬럼에 대해 참조할 수 있는 값이 없다, 순서가 잘못되었다란

이야기를 하고 있다.

 

/*+ MySQL 에서는 위 상황에 어떤 동작을 하는지 추가 확인해야겠지만 */ MariaDB 에서는

에러없이 수행되며 참조될 컬럼의 값이 미확정 된 시점이기에 NULL 값을 얻었다.

 

또 다음과 같이 INSERT 할 수 있다.

 

insert into test1 values(6),(7),(8);

 

또 다른 특징으로 데이터 길이를 넘는 경우 넘치는 만큼 데이터를 잘라 INSERT 한다.

 

MariaDB [test]> create table test7(id1 tinyint(3), id2 char(3)); Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> insert into test7 values (1234,'abcd'); Query OK, 1 row affected, 2 warnings (0.00 sec)   MariaDB [test]> show warnings; +---------+------+----------------------------------------------+ | Level   | Code | Message                                      | +---------+------+----------------------------------------------+ | Warning | 1264 | Out of range value for column 'id1' at row 1 | | Warning | 1265 | Data truncated for column 'id2' at row 1     | +---------+------+----------------------------------------------+ 2 rows in set (0.00 sec)   MariaDB [test]> select * from test7; +------+------+ | id1  | id2  | +------+------+ |  127 | abc  | +------+------+ 1 row in set (0.00 sec)

 

tinyint 의 경우 최대값이 127 이기에 해당 값이 insert 되었고,

char(3) 에 대해서는 길이를 넘는 부분이 잘려나갔다.

 

위 오퍼레이션은 strict sql_mode 에서는 실패한다.

 

INSERT 는 다음의 옵션을 지원한다.

n  LOW_PRIORITY

n  DELAYED

n  HIGH_PRIORITY

n  IGNORE

 

LOW_PRIORITY 와 HIGH_PRIORITY 는 TABLE LOCKING 을 사용하는 Stroage Engine 에 해당하는 이야기다.

 

LOW_PRIORITY INSERT 의 수행이 테이블을 읽는 다른 클라이언트가 없을 때 까지 연기된다.

DELAYED 의 경우 입력이 시작되면 완료까지 수행하지만 LOW_PRIORITY 는 수행을 일시중지한다.

때문에 사용이 활발한 TABLE 에 대한 INSERT … LOW_PRIORITY 는 굉장히 오랜시간동안 수행될 수 있다.

 

HIGH_PRIORITY 를 사용하는 경우 이는 –low-priority-updates 기능을 오버라이드하며

이 옵션과 함께 server 를 시작했다면, concurrent insert 사용이 불가능해진다.

 

DELAYED 를 사용하면 입력되어야 할 데이터는 버퍼에 입력되고, Client 는 다른 동작을 할 수 있다.

서버는 타겟 테이블이 사용중이면 입력을 대기하고, 아니라면 insert 를 시작한다.

입력 대기는 정기적인 확인을 통해 이루어지며, 사용중이라면 테이블 사용이 자유로워질 때까지

일시중지한다.

 

IGNORE 를 사용하면 ERROR 가 Warning 으로 취급된다.

Key 에 대해 Duplicate 된 대상을 이후 다시 조회해 보면 아무 변경이 없음을 알 수 있다.

 

MariaDB [test]> create table test8 (id1 int primary key, id2 int); Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> insert into test8 values (1,3); Query OK, 1 row affected (0.00 sec)   MariaDB [test]> insert ignore into test8 values (1,4); Query OK, 0 rows affected, 1 warning (0.00 sec)   MariaDB [test]> show warnings; +---------+------+---------------------------------------+ | Level   | Code | Message                               | +---------+------+---------------------------------------+ | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' | +---------+------+---------------------------------------+ 1 row in set (0.00 sec)   MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    3 | +-----+------+ 1 row in set (0.00 sec)

 

IGNORE 의 활용은 적합한 파티션이 없는 partitioned-table 에 대해서도 활용할 수 있다.

적합한 파티션이 없는 경우 일반적으로 에러와 함께 abort 되겠지만, IGNORE 를 사용하면 문제되는

rows 에 대해서만 조용히 Warning 처리되고 다른 rows 는 정상처리 된다.

 

ON DUPLICATE KEY UPDATE 는 중첩되는 키가 있는 경우 에러없이 처리하기 위한 방법이다.

중첩되는 키가 문제가 되는 케이스이기에 키를 업데이트 하게 된다.

 

다만 중첩되는 키가 발견되어 업데이트 한 결과물이 또 다른 키와 중첩되는 연쇄적인 상황에 대해서는

에러가 발생한다.

 

MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    4 | |   2 |    3 | +-----+------+ 2 rows in set (0.00 sec)   MariaDB [test]> insert into test8 values(1,4) on duplicate key update id1= id1 + 1; ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' MariaDB [test]> insert into test8 values(2,5) on duplicate key update id1= id1 + 1; Query OK, 2 rows affected (0.00 sec)   MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    4 | |   3 |    3 | +-----+------+ 2 rows in set (0.00 sec)   MariaDB [test]> insert into test8 values(2,5) on duplicate key update id1= id1 + 1; Query OK, 1 row affected (0.00 sec)   MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    4 | |   2 |    5 | |   3 |    3 | +-----+------+ 3 rows in set (0.00 sec)

 

7.    REPLACE Syntax

REPLACE [LOW_PRIORITY | DELAYED]     [INTO] tbl_name [(col_name,...)]     {VALUES | VALUE} ({expr | DEFAULT},...),(...),... Or: REPLACE [LOW_PRIORITY | DELAYED]     [INTO] tbl_name     SET col_name={expr | DEFAULT}, ... Or: REPLACE [LOW_PRIORITY | DELAYED]     [INTO] tbl_name [(col_name,...)]     SELECT ...

 

REPLACE 는 기본적으로 INSERT 와 동일한 기능을 한다.

차이는 INSERT IGNORE 의 경우 Duplicated row 에 대해 Warning 을 남기고 변경하지 않지만,

REPLACE 의 경우 Duplicated row 는 non-key columns 의 value 를 update 하고,

중첩되지 않는 Key value는 insert 한다.

 

결과적으로 INSERT 와 다른 점은 키가 중첩된 row 에 대한 처리방법이 다르다.

 

MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    4 | |   2 |    5 | |   3 |    3 | +-----+------+ 3 rows in set (0.00 sec)   MariaDB [test]>      MariaDB [test]> replace into test8 values(3,6); Query OK, 2 rows affected (0.00 sec)   MariaDB [test]> select * from test8; +-----+------+ | id1 | id2  | +-----+------+ |   1 |    4 | |   2 |    5 | |   3 |    6 | +-----+------+ 3 rows in set (0.00 sec)

 

8.    UPDATE syntax

UPDATE [LOW_PRIORITY] [IGNORE] table_reference     SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...     [WHERE where_condition]     [ORDER BY ...]     [LIMIT row_count] Multiple-table syntax: UPDATE [LOW_PRIORITY] [IGNORE] table_references     SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...     [WHERE where_condition]

 

ORACLE 과 다른 특이점은 없다.

LOW_PRIORITY, IGNORE 는 INSERT, UPDATE 옵션과 동일하다.

IGNORE 의 경우 UPDATE 의 결과로 Duplicated key 상태가 되는 ros 는 update 하지 않으며,

Update 로 인해 data conversion 이 일어나는 경우 가장 가까운 유효한 값으로 변환된다.

 

9.    Declaring variables on MariaDB & ORACLE

n  ORACLE   11:42:46 TEST2@TEST2>var tmp number 11:42:59 TEST2@TEST2>exec :tmp := 30;   PL/SQL procedure successfully completed.   Elapsed: 00:00:00.00 11:43:11 TEST2@TEST2>select :tmp from dual;         :TMP ----------         30   Elapsed: 00:00:00.01   n  MariaDB   MariaDB [test]> select 30 into @tmp from dual; Query OK, 1 row affected (0.00 sec)   MariaDB [test]> select @tmp; +------+ | @tmp | +------+ |   30 | +------+ 1 row in set (0.00 sec)   MariaDB [test]> set @hello='hi'; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> select @hello from dual; +--------+ | @hello | +--------+ | hi     | +--------+ 1 row in set (0.00 sec)

 

10. References

A.     Data Manipulation Statements | http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-data-manipulation.html

B.     13.2.9.2. JOIN Syntax | http://dev.mysql.com/doc/refman/5.5/en/join.html

C.     13.2.2. DELETE Syntax | http://dev.mysql.com/doc/refman/5.5/en/delete.html

D.     13.2.5. INSERT Syntax | http://dev.mysql.com/doc/refman/5.5/en/insert.html

E.     13.2.8. REPLACE Syntax | http://dev.mysql.com/doc/refman/5.5/en/replace.html

F.     13.2.11. UPDATE Syntax | http://dev.mysql.com/doc/refman/5.6/en/update.html