Adding and deleting columns on an existing mysql table is done with the alter table syntax. This mysql tutorial should show you how to easily acomplish your goals, whether it being to delete or add columns – lets show some real examples.
Keep in mind that these SQL statements can also be executed from a terminal or command prompt, after connecting to your MySQL server, just remember to add a semicolon to the end of the querys.
Adding columns to a MySQL table
There is no add column syntax, but as mentioned ealier, you can use the alter table syntax as demonstrated in the below example – running the below query will add the column to the end of the table.
ALTER TABLE table_name ADD column_name datatype
Replace table_name with the name of your table, and column_name with the name of the new column. The datatype may be a bit more difficult to deside, and it will not be covered in detail here. In general, use varchar(1-255) if you know the length of the longest string to be stored, and text if you are storing something longer. Keep in mind that you can also use an integeer datatypes if dealing with numbers.
Delete column using alter table
The alter table syntax is also used when deleting columns, there isn't really that much you need to know here – just be careful not to delete the wrong column :-)
ALTER TABLE table_name DROP COLUMN column_name
Again, just replace table_name with the name of your table, and column_name with the name of the column that you want to be removed from your table.
For example, if you have the below table and run following MySQL query, all the names will be deleted from the database.
ALTER TABLE workers DROP COLUMN Name
Resulting in the following table – all names removed with a single SQL line!
Change the datatype of a column
To change the datatype of a mysql table column you will also be using the alter table syntax. For example, this can be useful if you started out by creating a table with a text column, and later figured out that varchar would be more than enough to store the data.
ALTER TABLE workers ALTER COLUMN Name varchar(100)
But if you need to do this, it might also be a signal that you haven't really thought enough about your database and table structure.
Set collation of a mysql table
Keep in mind that the ci collation versions are case insensetive, you need to use the bin version to have case sensitivity when performing querys. It is quite normal to have utf8_general_ci or latin1_general_ci for example, you can change this to utf8_bin by running a alter table query.
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin
Beware that this may take some time on large tables!
Running these querys from php
Finally, for those of you having trouble with the PHP syntax. In PHP you will likely need to do something like below:
query("ALTER TABLE workers ALTER COLUMN Name varchar(100)"); // The OOP syntax would likely be as follows $db->query("ALTER TABLE workers ALTER COLUMN Name varchar(100)");