PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites

27 Dec 2013

Move columns in mysql table

                       You can change the column name and change the column size in mysql. But can you rearrange the column order in mysql table. Yes, you can rearrange it in mysql. It can be done by 'ALTER', 'CHANGE' and 'AFTER' mysql command. The mysql query for move the column in mysql table is:

                   " ALTER TABLE table_name CHANGE column_name column_name datatype(length) AFTER column_name"

                       You can also done by 'MODIFY' mysql command. The mysql query as follows as:

                   " ALTER TABLE table_name MODIFY column_name datatype(length) AFTER column_name"

Consider the following example: table1 is the name of table.
                   
rearrange columns in mysql

                       Now the column 'name' is moved after column 'img'. The mysql query as follows as:

                   " ALTER TABLE table1 CHANGE name name varchar(30) AFTER img"

Now you'll get output like this:

move column using MODIFY mysql command

The column name 'name' is placed after 'img' in mysql table 'table1'.


Move  multiple columns at the time in mysql:


                             You can move multiple columns at the time in mysql. The column name order id, name ,img in table1 is changed into img, name ,id. The mysql query as follows as:

                         "ALTER TABLE table1 CHANGE id id int(5) AFTER img,
                                         CHANGE name name varchar(30) AFTER img"

Now you'll get output like this:

move multiple columns at a time in mysql using CHANGE command