Quickly add a copy of a column to a MySQL table
I need a fast way duplicate a DA开发者_JAVA百科TETIME column in a table and give it a new name.
I have a column named myDate in my table called myResults, I need a query to make a new column in the table called newDate which has the exact same data as the myDate column.
Is there a faster way to do this than by doing the obvious 2 step approach of make a new column, and then copying all the data (it's a large table and I'm looking for the fastest approach)?
Obvious solution:
ALTER TABLE `myResults` ADD `newDate` DATETIME;
UPDATE `myResults` SET `newDate` = `myDate`;
UPDATE `table_name` SET `new_column` = `existing_column` WHERE `id`=`id`
The obvious solution is the only solution, unfortunately.
However note that in general you shouldn't be copying a column in relational databases.
If you just need a default in there, you can either choose what the default is statically or use a function call.
ALTER TABLE `myResults` ADD `newDate` DATETIME DEFAULT '2010-01-01';
or
ALTER TABLE `myResults` ADD `newDate` DATETIME DEFAULT current_timestamp;
Why would your workload ever demand a new datetime column, that duplicates another columns data? This sounds like horrable practice? How about telling us what you're trying to achieve? You can pull a second column with the same data in a few different ways, without actually duplicating the data:
SELECT date1 AS date_old, date1 AS date_new FROM table;
-or-, you can create a view
CREATE VIEW virtual_table AS
SELECT date1 AS date_old, date1 AS date_new FROM table
;
SELECT * FROM virtual_table;
精彩评论