James Black <[EMAIL PROTECTED]> wrote on 06/23/2005 03:41:32 PM: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1
> How would I add a comment to rows in an existing table, if I am not > changing the row definition? > This is for mysql 4.1 and 5.0. > Thanx. > - -- > "Love is mutual self-giving that ends in self-recovery." Fulton Sheen > James Black [EMAIL PROTECTED] > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.1 (MingW32) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > iD8DBQFCuxBsikQgpVn8xrARAvsRAJ0YrVwCPorS2914+jwLA9sWKt7gYQCglFo+ > ojh1r5Skifmg83MZAcVonO4= > =2WLs > -----END PGP SIGNATURE----- You still need to ALTER TABLE and use the CHANGE predicate. You are CHANGE-ing a column into it's own definition, almost. The definition is going to be different because you are adding a comment to the row. mysql>show create table sampleresult \G *************************** 1. row *************************** Table: sampleresult Create Table: CREATE TABLE `sampleresult` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `Report_ID` int(11) unsigned default NULL, `TestGroup` varchar(30) NOT NULL default '', `TestKey` varchar(30) NOT NULL default '', `Value` decimal(11,4) NOT NULL default '0.0000', `Units` varchar(10) NOT NULL default '', `AnalyzedByPerson_ID` int(11) unsigned default NULL, PRIMARY KEY (`ID`), ) Now let's say I wanted to add a comment to the TestGroup field (all one big statement, ignore the message wrapping): ALTER TABLE sampleresult CHANGE TestGroup TestGroup varchar(30) NOT NULL default '' COMMENT 'column comment here'; I am not sure about MyISAM but when I do this on Innodb, it creates a "hidden" table with the new definition, populates the new table with the old data, then swaps names. I found this out when my disk ran out of room during an ALTER TABLE (using per-file tablespaces) because someone had put an 8GB backup file on there and didn't clear it off. With all of the other info in that disk (including the backup file), I only had 256MB of free space to work with and the table I wanted to alter took up about 450MB. The warning is: make sure you have enough room for a copy of your table before you begin to alter it. Also because the ALTER is going to copy the data anyway, why make it do it once for EACH COLUMN? Add comments to all of the columns that need it in a single statement by separating your CHANGE predicates with commas ALTER TABLE tablename CHANGE field1 ..., CHANGE field2 ..., CHANGE field3 ..., etc... That way you take care of it all at once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
