1

I have a mysql table adv(id(TEXT),index(TEXT) ) which I need to change it as follows

ALTER TABLE adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

will this preserve all the data in the above columns assuming that they are less then 80 chars for id and a number for index?

  • You should consider adding NOT NULL and UNSIGNED if appropriate. – Rick James Jul 15 '16 at 4:48
3

Yes, it will. When there's no load on the table, I would suggest following procedure though. Not only can you double-check if everything is okay, it probably is also faster.

CREATE TABLE new_adv LIKE adv;

ALTER TABLE new_adv
  MODIFY COLUMN id VARCHAR(80),
  MODIFY COLUMN index INT(11);

INSERT INTO new_adv SELECT * FROM adv;

RENAME adv TO old_adv, new_adv TO adv;
/*when everything is okay...*/
DROP TABLE old_adv;

And by the way, it's a good idea not to use keywords like index for column names. You might want to change that, too. And if it's just for the benefit of not having to use backticks all the time when you use this column.

| improve this answer | |
  • what will be lost? – db101 Jul 13 '16 at 7:48
  • i have issued the command in question can i stop it now? – db101 Jul 13 '16 at 7:48
  • Nothing will be lost, it should say "yes, it will preserve all data". Which command have you executed, and why would you want to stop it? – tombom Jul 13 '16 at 7:49
  • so the existing cols will be cast to varchar and int from TEXT ? if i use the command in question – db101 Jul 13 '16 at 7:49
  • 1
    Why don't you just try it out? Is it a live system you're working on? I personally try almost everything on a dev system first. – tombom Jul 13 '16 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.