mariadb root@localhost:dyw> select * from animals; +----+---------+------+ | id | name | foot | +----+---------+------+ | 1 | dog | 4 | | 2 | cat | 4 | | 3 | chicken | 2 | | 15 | bird | 2 | | 16 | cat | 4 | +----+---------+------+ 5 rows in set Time: 0.031s
mariadb root@localhost:dyw> create table tmp like animals; Query OK, 0 rows affected Time: 0.053s
mariadb root@localhost:dyw> insert into tmp(id,name,foot) select id,name,foot fr om animals group by name,foot; Query OK, 4 rows affected Time: 0.024s
mariadb root@localhost:dyw> select * from tmp; +----+---------+------+ | id | name | foot | +----+---------+------+ | 1 | dog | 4 | | 2 | cat | 4 | | 3 | chicken | 2 | | 15 | bird | 2 | +----+---------+------+ 4 rows in set Time: 0.024s
mariadb root@localhost:dyw> show create table tmp\G ***************************[ 1. row ]*************************** Table | tmp Create Table | CREATE TABLE `tmp` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `foot` tinyint(3) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set Time: 0.018s
mariadb root@localhost:dyw> drop table animals; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 0.014s
mariadb root@localhost:dyw> alter table tmp rename to animals; You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 0.011s
mariadb root@localhost:dyw> select * from animals; +----+---------+------+ | id | name | foot | +----+---------+------+ | 1 | dog | 4 | | 2 | cat | 4 | | 3 | chicken | 2 | | 15 | bird | 2 | +----+---------+------+ 4 rows in set Time: 0.026s