Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides

FAQ
It is currently Wed Oct 18, 2017 5:02 pm


Mysql Application & Query Optimization, Questions, Indexes, Bugs.

Author Message
debuser
Post  Post subject: Mysql: add column to table  |  Posted: Tue May 18, 2010 11:09 am

Joined: Thu Aug 06, 2009 2:48 am
Posts: 105

Offline
 

Mysql: add column to table

Adding a column to an existing mysql table is simple.

Below, I'm showing information regarding a phpbb table that I needed to alter, in order to upgrade phpbb:
Code:
mysql> describe phpbb_forums;
+--------------------------+-----------------------+------+-----+---------+----------------+
| Field                    | Type                  | Null | Key | Default | Extra          |
+--------------------------+-----------------------+------+-----+---------+----------------+
| forum_id                 | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| parent_id                | mediumint(8) unsigned | NO   |     | 0       |                |
| left_id                  | mediumint(8) unsigned | NO   | MUL | 0       |                |
| right_id                 | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_parents            | mediumtext            | NO   |     | NULL    |                |
| forum_name               | varchar(255)          | NO   |     |         |                |
| forum_desc               | text                  | NO   |     | NULL    |                |
| forum_desc_bitfield      | varchar(255)          | NO   |     |         |                |
| forum_desc_options       | int(11) unsigned      | NO   |     | 7       |                |
| forum_desc_uid           | varchar(8)            | NO   |     |         |                |
| forum_link               | varchar(255)          | NO   |     |         |                |
| forum_password           | varchar(40)           | NO   |     |         |                |
| forum_style              | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_image              | varchar(255)          | NO   |     |         |                |
| forum_rules              | text                  | NO   |     | NULL    |                |
| forum_rules_link         | varchar(255)          | NO   |     |         |                |
| forum_rules_bitfield     | varchar(255)          | NO   |     |         |                |
| forum_rules_options      | int(11) unsigned      | NO   |     | 7       |                |
| forum_rules_uid          | varchar(8)            | NO   |     |         |                |
| forum_topics_per_page    | tinyint(4)            | NO   |     | 0       |                |
| forum_type               | tinyint(4)            | NO   |     | 0       |                |
| forum_status             | tinyint(4)            | NO   |     | 0       |                |
| forum_posts              | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_topics             | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_topics_real        | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_last_post_id       | mediumint(8) unsigned | NO   | MUL | 0       |                |
| forum_last_poster_id     | mediumint(8) unsigned | NO   |     | 0       |                |
| forum_last_post_subject  | varchar(255)          | NO   |     |         |                |
| forum_last_post_time     | int(11) unsigned      | NO   |     | 0       |                |
| forum_last_poster_name   | varchar(255)          | NO   |     |         |                |
| forum_last_poster_colour | varchar(6)            | NO   |     |         |                |
| forum_flags              | tinyint(4)            | NO   |     | 32      |                |
| forum_options            | int(20) unsigned      | NO   |     | 0       |                |
| display_subforum_list    | tinyint(1) unsigned   | NO   |     | 1       |                |
| display_on_index         | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_indexing          | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_icons             | tinyint(1) unsigned   | NO   |     | 1       |                |
| enable_prune             | tinyint(1) unsigned   | NO   |     | 0       |                |
| prune_next               | int(11) unsigned      | NO   |     | 0       |                |
| prune_days               | mediumint(8) unsigned | NO   |     | 0       |                |
| prune_viewed             | mediumint(8) unsigned | NO   |     | 0       |                |
| prune_freq               | mediumint(8) unsigned | NO   |     | 0       |                |
+--------------------------+-----------------------+------+-----+---------+----------------+
42 rows in set (0.01 sec)


The column that I want to add is "forum_recent_topics" and I want to add it after the existing column "prune_freq"(i.e.).

Below command will add "forum_recent_topics" column, right after "prune_freq":
Code:
mysql> ALTER TABLE phpbb_forums ADD forum_recent_topics tinyint(1) AFTER prune_freq;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


If I wanted the new column to be first:
Code:
mysql> ALTER TABLE phpbb_forums ADD forum_recent_topics tinyint(1) FIRST;





Top
sonee4
Post  Post subject: Re: Mysql: add column to table  |  Posted: Fri Oct 08, 2010 1:38 am

Joined: Thu Oct 07, 2010 11:25 pm
Posts: 3

Offline
Great info shared by you, keep posting like this.


Top
Display posts from previous:  Sort by  
E-mail friendPrint view

Topics related to - "Mysql: add column to table"
 Topics   Author   Replies   Views   Last post 
There are no new unread posts for this topic. Mysql add Unique index to a table column

debuser

0

1405

Fri Aug 06, 2010 10:01 am

debuser View the latest post

There are no new unread posts for this topic. Mysql remove column from an existing table

debuser

0

744

Fri Aug 06, 2010 10:06 am

debuser View the latest post

There are no new unread posts for this topic. Mysql - Change position of a column after another column

admin

1

1897

Fri Oct 08, 2010 1:41 am

sonee4 View the latest post

There are no new unread posts for this topic. Mysql change column's default value

debuser

0

1131

Thu Aug 12, 2010 3:43 am

debuser View the latest post

There are no new unread posts for this topic. Rename MySql table

debuser

1

590

Fri Oct 08, 2010 1:36 am

sonee4 View the latest post

There are no new unread posts for this topic. MySql rename table

admin

2

930

Mon Nov 28, 2011 3:39 am

Darwin View the latest post

There are no new unread posts for this topic. How to calculate mysql time difference between unix timestamp column and now() format

mandrei99

0

4685

Fri May 31, 2013 4:10 am

mandrei99 View the latest post

There are no new unread posts for this topic. Mysql> select records that have date/datetime column equal to today's date

admin

0

1800

Tue Nov 22, 2011 9:40 am

admin View the latest post

There are no new unread posts for this topic. MySql doesn't start: libexec/mysqld: File './mysql-bin.index' not found (Errcode: 13)

debuser

0

1204

Mon Feb 22, 2010 1:43 pm

debuser View the latest post

There are no new unread posts for this topic. How to use a mysql stored procedure

debuser

1

595

Tue Nov 22, 2011 1:23 am

Darwin View the latest post

 

Who is online
Users browsing this forum: No registered users and 0 guests
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum
Jump to:  
cronNews News Site map Site map SitemapIndex SitemapIndex RSS Feed RSS Feed Channel list Channel list


Delete all board cookies | The team | All times are UTC - 5 hours [ DST ]



phpBB SEO