MySQL Alter Table Add Field Before or After a field already present
$query = "ALTER TABLE `" . $table_prefix . "posts_to_bookmark`
ADD COLUMN `ping_status` INT(1) NOT NULL
AFTER `<TABLE COLUMN BEFORE THIS COLUMN>`";
I believe you need to have ADD COLUMN
and use AFTER
, not BEFORE
.
In case you want to place column at the beginning of a table, use the FIRST
statement:
$query = "ALTER TABLE `" . $table_prefix . "posts_to_bookmark`
ADD COLUMN `ping_status` INT(1) NOT NULL
FIRST";
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
MySQL: ALTER TABLE if column not exists
Use the following in a stored procedure:
IF NOT EXISTS( SELECT NULL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'tablename'
AND table_schema = 'db_name'
AND column_name = 'columnname') THEN
ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';
END IF;
Adding multiple columns AFTER a specific column in MySQL
Try this
ALTER TABLE users
ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,
ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,
ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;
check the syntax
How to insert columns at a specific position in existing table?
ALTER TABLE
by default adds new columns at the end of the table. Use the AFTER
directive to place it in a certain position within the table:
ALTER table table_name
Add column column_name57 integer AFTER column_name56
From mysql doc
To add a column at a specific position within a table row, use
FIRST
orAFTER
col_name
. The default is to add the column last. You can also useFIRST
andAFTER
inCHANGE
orMODIFY
operations to reorder columns within a table.
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
I googled for this for PostgreSQL but it seems to be impossible.
add column to mysql table if it does not exist
Note that INFORMATION_SCHEMA
isn't supported in MySQL prior to 5.0. Nor are stored procedures supported prior to 5.0, so if you need to support MySQL 4.1, this solution isn't good.
One solution used by frameworks that use database migrations is to record in your database a revision number for the schema. Just a table with a single column and single row, with an integer indicating which revision is current in effect. When you update the schema, increment the number.
Another solution would be to just try the ALTER TABLE ADD COLUMN
command. It should throw an error if the column already exists.
ERROR 1060 (42S21): Duplicate column name 'newcolumnname'
Catch the error and disregard it in your upgrade script.
Adding a new SQL column with a default value
Try this:
ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;
From the documentation that you linked to:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
...
ADD [COLUMN] (col_name column_definition,...)
...
To find the syntax for column_definition
search a bit further down the page:
column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.
And from the linked page:
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
Notice the word DEFAULT there.
Related Topics
PHP How to Get the Base Domain/Url
PHP Find All Occurrences of a Substring in a String
Execute a PHP Script from Another PHP Script
How to Use Two Submit Buttons, and Differentiate Between Which One Was Used to Submit the Form
MySQL PHP - Select Where Id = Array()
I Have a Base64 Encoded Png, How to Write the Image to a File in PHP
Get Date for Monday and Friday for the Current Week (Php)
How to Send Soap Xml via Curl and PHP
Codeigniter - File Upload Required Validation
Simple Comet Example Using PHP and Jquery
Codeigniter - Correct Way to Link to Another Page in a View
Codeigniter Assets Folder Best Practice
How to Use PHP in a JavaScript Function
Twilio PHP - Ssl Certificate: Self Signed Certificate in Certificate Chain
Arabic Characters in JSON Decoding
Using Laravel Socialite to Login to Facebook