Question: I know you can ALTER the column order in MySQL with FIRST and AFTER, but why would you want to bother? Since good queries explicitly name columns when inserting data, is there really any reason to care what order your columns are in in the table?

Answer: 
Some badly-written applications might be dependent on column order / index instead of column name. They shouldn't be, but it does happen. Changing the order of the columns would break such applications.
If you're going to be using UNION a lot, it makes matching columns easier if you have a convention about their ordering.