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>