IF EXISTS

If you drop a table and it does not exist, you will get an error message but, if you include the if exists clause, you will only get a warning:
  

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| andrews_table  |
+----------------+
1 row in set (0.00 sec)

mysql> drop table andrews_table;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> drop table andrews_table;
ERROR 1051 (42S02): Unknown table 'andrews_table'
mysql> drop table if exists
    -> andrews_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'andrews_table' |
+-------+------+-------------------------------+
1 row in set (0.00 sec)

mysql>

Modifying Column Definitions in MySQL

If you change a column definition, MySQL converts the data in that column for you. If it finds data which it cannot convert, it displays a warning and you can see details by using the show warnings command. In the example, a varchar column is converted to int. MySQL is unable to convert the value ABC so it sets it to zero instead:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into andrews_table

    -> values ('ABC');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table

    -> values ('123');

Query OK, 1 row affected (0.00 sec)

 

mysql> alter table andrews_table

    -> change col1 col1 int;

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

(N.B. I have reformatted the output from the show warnings command to make it fit on the page.)

mysql> show warnings;

+---------+------+-----------------------------------+

| Level   | Code | Message                           |

+---------+------+-----------------------------------+

| Warning | 1366 | Incorrect integer value:          |
|         |      | 'ABC' for column 'col1' at row 1  |

+---------+------+-----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_table;

+------+

| col1 |

+------+

|    0 |

|  123 |

+------+

2 rows in set (0.00 sec)

 

mysql>

MyISAM versus InnoDB Tables in MySQL

This is a worked example comparing 2 MySQL table types:

1. MyISAM – you cannot rollback DML on these tables.
2. InnoDB – you can (sometimes) rollback DML on these tables.

First, create a table of each type:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10370

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_myisam

    -> (col1 varchar(10)) engine = myisam;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create table andrews_innodb

    -> (col1 varchar(10)) engine = innodb;

Query OK, 0 rows affected (0.38 sec)

 

mysql>

 

Then insert a row in each table and rollback the insert. Note the warning that you cannot rollback non-transactional tables:

 

mysql> insert into andrews_myisam values ('MYISAM');

Query OK, 1 row affected (0.06 sec)

 

mysql> insert into andrews_innodb values ('INNODB');

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

 

Query the tables and note that the rollback has failed in BOTH tables and the inserted rows are still there:

 

mysql> select * from andrews_myisam;

+--------+

| col1   |

+--------+

| MYISAM |

+--------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

This is because MySQL’s default behaviour is to commit DML immediately. To alter this, you need to set autocommit = 0. Do this then repeat the test. This time the DML deletes the rows inserted above:

 

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from andrews_myisam;

Query OK, 1 row affected (0.00 sec)

 

mysql> delete from andrews_innodb;

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_myisam;

Empty set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

The delete statement on the MyISAM table could not be rolled back, as you might expect. Setting autocommit to zero prevented the delete statement on the InnoDB being committed immediately. The rollback therefore worked and the inserted row reappeared in the table.