How to change data types in fields in a table in mysql at once

Question

I have the following table:

 create table pessoas(
 id tinyint,
 nome char,
 peso float,
 altura float(3),
 sexo int(1),
 nacionalidade varchar (20)
 );

But I want to change all the fields at once

alter table pessoas modify nome varchar (20) not null;
alter table pessoas modify peso decimal(5,3);
alter table pessoas modify sexo enum ('M','F');

Anyway, you have to switch to a command only the "modify"?

Answer 1

Yes, there is.

The SQL standard defines a change only each ALTER TABLE, but Mysql extended the default to allow this syntax:

ALTER TABLE PESSOAS MODIFY
   nome VARCHAR(20) NOT NULL,
   peso DECIMAL(5,3),
   sexo ENUM('M','F')
;

You can even change the order of the columns, with AFTER or BEFORE, for example:

ALTER TABLE PESSOAS MODIFY
   nome VARCHAR(20) NOT NULL,
   peso DECIMAL(5,3),
   sexo ENUM('M','F') AFTER nome
;

In this last example, in addition to changing the types, the order of the columns will be nome,sexo and peso.

More details in the manual:
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

And the part that matters most to what was asked is this one:

Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a single ALTER TABLE statement, separated by commas. This is a Mysql Extension to standard SQL, which Permits only one of each clause per ALTER TABLE statement. For example, to drop Multiple Columns in a single statement, do this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;

Which means something like that:

Multiple clauses ADD, ALTER, DROP, and CHANGE are allowed in a single ALTER TABLE command, separated by commas. This is a Mysql extension relative to the SQL standard, which allows only one change per ALTER TABLE statement command. For example, to remove multiple columns in a single command, it looks like this:

ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;