Reorder mysql table columns

How to reorder MySQL table columns in MySQL databases

The data structure of any application is the hardest part to change. Nevertheless, new needs arise all the time, and so, adding new table columns to an existing MySQL database is a common task.

But what about reordering the table column order in MySQL after you have already created and added the new columns to an existing table? Doing so is an advisable procedure in order to keep together very related data fields in your MySQL table structure.

While I tend to perform many MySQL database management tasks using phpMyAdmin, I haven't found any phpMyAdmin feature to change the column order of an existing database. Fortunately, you just need to execute a very simple MySQL instruction to specify a new column order:

ALTER table `table_name`
       MODIFY COLUMN `column_name` your_data_type
       AFTER `other_column_name`

Just replace the following fields by your actual MySQL data:

• table_name: the name of your MySQL table being modified.
• column_name: the name of the MySQL table column that you want to reorder.
• your_data_type: the MySQL data type of the data stored in the moved column, such as int, varchar(lenght), text, etc.
• other_column_name: the column name that will be just before the new position of your reordered column.

A MySQL table column reorder example

Here is an example of an actual MySQL query to change the table column order. Let's say I want to move the column called "user_password" just after the "user_name" column to keep strongly related columns grouped:

ALTER table `registered_users`
       MODIFY COLUMN `user_password` varchar(25)
       AFTER `user_name`

This will reorder the columns in your current MySQL table structure, but it will not alter the row order of your table records: the stored data order will remain unaltered. Reorder MySQL table columns to visually group strongly related fields. So reordering is just a way of changing the column order of an existing MySQL table, and it won't modify your query performance.

Column reorder is mainly a matter of coherence and logical organization. On the contrary, altering the stored data order may optimize (or negatively impact) such MySQL query performance.

7 comments

Permalink

Posted by ESV

MySQL set auto increment in phpMyAdmin

How to set MySQL auto increment in phpMyAdmin

A common practice in database design is to set primary keys (PKs) with auto increment enabled. This way, you don't have to worry about specifying a new unique primary key value each time you insert a new record in such table.

While phpMyAdmin is a very powerful and easy to use MySQL database management tool, "where could I set auto increment in phpMyAdmin" is still a frequent question. And here is the solution.

In the latest phpMyAdmin versions there is a new A_I Checkbox. Mark this option as enabled when creating or editing your primary key table column and that numeric field will automatically increment its value each time a new record is inserted.

You can check that the auto increment property was successfully setup in the EXTRA column of the table column properties (after selecting a table, inside the structure tab). If the auto_increment text appears here, the configuration was successful.

In previous phpMyAdmin versions, auto_increment was an additional option inside the dropdown menu of the EXTRA category (the last column in the field creation menu). To access the "edit table field menu" you can click the pencil icon in the desired table field row, inside the Structure tab.

Anyway, you can always run an SQL command to update the auto increment status of the desired column by selecting the SQL tab and writing an SQL query like this one:

ALTER TABLE `table_name`
    CHANGE `pk_column_name` `pk_column_name`
           INT(key_length) NOT NULL AUTO_INCREMENT

Just replace "table_name" by the name of the current table being edited, "pk_column_name" by the column name of your primary key column and "key_length" by your integer primary key length (the default int length is 11).

You should also make sure that the auto incrementing field is in fact the primary key of the current table. You can reset the table primary key by clicking on the table key icon of the desired field row.

Take also into account that only one auto incrementing field may be specified in each MySQL table. Nevertheless, auto incrementing only makes sense when used with numeric primary keys, and only a primary key field may be specified for each MySQL table as well.

Finally, if you want to change the current Auto Increment Value (i.e.: to make your auto increment field start from a specific numeric value) just select the phpMyAdmin Operations tab, type in the AUTO_INCREMENT field the new starting value of your auto incrementing field, and you are ready to go.

9 comments

Permalink

Posted by ESV

XAMPP MySQL deactivated fix

XAMPP MySQL deactivated error

Installing XAMPP is a quick way to setup an Apache Server with PHP and a MySQL database.

But sometimes there's some kind of configuration error or bug that doesn't allow to start the MySQL service. Checking the status with the XAMPP control panel will display a message of "MySQL database DEACTIVATED".

How to fix the XAMPP MySQL deactivated bug

While I'm not sure about the causes of this error, there is a quick fix to activate MySQL in XAMPP following 2 simple steps.

• First, try not to configure your MySQL database as a service. This is asked during the XAMPP installation. So don't select the checkbox of installing MySQL databases as a system service.
• Second, create a basic configuration file for your new MySQL installation (you can do this automatically using Win MySQL admin).

Yes, there's an automated way to generate a basic config file for MySQL using tools installed by default in XAMPP. Just go to [XAMPP installation folder] / mysql / bin and run winmysqladmin.exe. Type a new username and password, and let the MySQL admin program generate the default mysql initialization file.

And that's it! A MySQL disabled as a Windows service, and a default configuration file with MySQL username and password defined should change your XAMPP SQL status from "MySQL database deactivated" to MySQL database ACTIVATED.

How to check that MySQL status is active

You can check the status of the MySQL databases opening the XAMPP control panel: MySQL should have a green running tag next to it.

Notice that I wouldn't keep using the Win MySQL Admin once you turned your MySQL service active: it's an old, discontinued application that seems to throw random Windows errors. But don't worry: just close the Win MySQL Admin window and keep using the built-in phpMyAdmin from the XAMPP default installation.

You can open the phpMyAdmin menu to check the active status of the MySQL databases from the XAMPP Apache main menu: start the Apache server, open the main server page using your web browser (just type "localhost" by default in your web browser address bar) and click the phpMyAdmin link to check that your MySQL databases are enabled.

I hope this helped you to activate your MySQL databases in XAMPP! Now, have a happy coding and local server testing!

11 comments

Permalink

Posted by ESV