CODEWITHSUNDEEP
mysql
Martin
Martin

Reputation: 10563

Update mysql table

I have a table that was created using this mysql statement below.

CREATE TABLE `jos_ttvideo` (
 `id` int(11) NOT NULL auto_increment,
 `video_id` int(11) NOT NULL DEFAULT 0,
 `thumbnail` varchar(255) NOT NULL DEFAULT "",
 `thumbnail_med` varchar(255) NOT NULL DEFAULT "",
 `thumbnail_lrg` varchar(255) NOT NULL DEFAULT "",
 `title` varchar(50) NOT NULL DEFAULT "",
 `author` varchar(50) NOT NULL DEFAULT "",
 `description` varchar(255) NOT NULL DEFAULT "",
 `full_description` text NOT NULL DEFAULT "",
 `c_date` DATETIME NOT NULL,
 `plays` int(11) NOT NULL DEFAULT 0,
 `likes` int(11) NOT NULL DEFAULT 0,
 `width` int(4) NOT NULL DEFAULT 600,
 `height` int(4) NOT NULL DEFAULT 300,
 `published` tinyint(1) NOT NULL DEFAULT 0,
 `checked_out` tinyint(1) NOT NULL DEFAULT 0,
 `catid` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY  (`id`)
);

Now I want to create a mysql update script that that will preserve any data contained in the table and add & update certain columns. So once done the table structure will look like below:

CREATE TABLE IF NOT EXISTS `jos_ttvideo` (
 `id` int(11) NOT NULL auto_increment,
 `video_id` varchar(255) NOT NULL DEFAULT 0,
 `site` varchar(20) NOT NULL DEFAULT "",
 `thumbnail` varchar(255) NOT NULL DEFAULT "",
 `thumbnail_med` varchar(255) NOT NULL DEFAULT "",
 `thumbnail_lrg` varchar(255) NOT NULL DEFAULT "",
 `title` varchar(50) NOT NULL DEFAULT "",
 `author` varchar(50) NOT NULL DEFAULT "",
 `description` varchar(255) NOT NULL DEFAULT "",
 `full_description` text NOT NULL DEFAULT "",
 `c_date` DATETIME NOT NULL,
 `plays` int(11) NOT NULL DEFAULT 0,
 `likes` int(11) NOT NULL DEFAULT 0,
 `width` int(4) NOT NULL DEFAULT 600,
 `height` int(4) NOT NULL DEFAULT 300,
 `custom_dimensions` tinyint(1) NOT NULL DEFAULT 0,
 `published` tinyint(1) NOT NULL DEFAULT 0,
 `checked_out` tinyint(1) NOT NULL DEFAULT 0,
 `catid` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY  (`id`)
);

Essentially there are 2 ways I can think of doing this:

  1. create a temp table using the 2nd mysql script, copy data from the original table to the temp one, drop the original one, rename the temp table to the original table name
  2. run an alter table statement for each column change and add of column.

What is the best way to do this? Because there are only a few changes to the table structure I think option 2 would be an easier route. However, should there be many more changes as could well be the case in the future, then I think option 1 would be better. If I go with option 1, what mysql is needed to copy the original table data to the temp table?

Thanks for the help!

Upvotes: 0

Views: 341

Answers (2)

ajreal
ajreal

Reputation: 47321

I having different thought,
Depends on amount of the records reside in the table.
if lots of record, alter column by column will be very slow,
mysql will work harder for data write and at the same-time updating the index

Option 1 is more clean-cut,
create the table B with the new structure,
do a insert select to table B from table A,
after that swap the name

PS: minimal table write lock

Upvotes: 1

AndreKR
AndreKR

Reputation: 33678

As long as you have/can create the ALTER TABLE there is no need for option 1 at all, because that's exactly what MySQL does by itself when you use option 2.

Upvotes: 1

Related Questions