Use if not exists to create a data table

If you simply use the following SQL statement, failure may be returned. The cause of failure is that the data table already exists.

Create table sales3 (id NUMERIC, sale_date datetime not null)
PARTITION by range (TO_DAYS (sale_date ))
(
PARTITION sales20140301 values less than (TO_DAYS ('2017-03-01 ')),
PARTITION sales20140401 values less than (TO_DAYS ('2017-04-01 '))
)

The prompt statement returned during execution is as follows:

[SQL] create table sales3 (id NUMERIC, sale_date datetime not null)
PARTITION by range (TO_DAYS (sale_date ))
(
PARTITION sales20140301 values less than (TO_DAYS ('2017-03-01 ')),
PARTITION sales20140401 values less than (TO_DAYS ('2017-04-01 '))
)

[Err] 1050-Table 'sales3' already exists

IF you use if not exists to create a data table, the operation is successful even IF the table already EXISTS:

Create table if not exists sales3 (id NUMERIC, sale_date datetime not null)
PARTITION by range (TO_DAYS (sale_date ))
(
PARTITION sales20140301 values less than (TO_DAYS ('2017-03-01 ')),
PARTITION sales20140401 values less than (TO_DAYS ('2017-04-01 '))
)

The returned result is as follows:

[SQL] create table if not exists sales3 (id NUMERIC, sale_date datetime not null)
PARTITION by range (TO_DAYS (sale_date ))
(
PARTITION sales20140301 values less than (TO_DAYS ('2017-03-01 ')),
PARTITION sales20140401 values less than (TO_DAYS ('2017-04-01 '))
)

Affected rows: 0
Date: 0.003 ms

This article permanently updates the link address: