MySql ALTER TABLE syntax

bulliver · 08-15-2006, 04:27 PM

For my website I rolled my own comments feature. The problem is that one of the fields is for the date, and is a varchar(30). However, 30 chars is too short for some dates and the end is getting truncated, so I want to change it to a varchar(45).

The ALTER TABLE statement seems to be what I need, but I know MySql is very unforgiving so I wanted to get some confirmation from an expert before pressing enter...

So, given that my table is 'comments' and the field is 'date' I would want to do:

Code:

mysql> ALTER TABLE comments MODIFY date VARCHAR(45);

Is this right?

cxel91a · 08-15-2006, 06:33 PM

Quote:

Originally Posted by bulliver

For my website I rolled my own comments feature. The problem is that one of the fields is for the date, and is a varchar(30). However, 30 chars is too short for some dates and the end is getting truncated, so I want to change it to a varchar(45).

The ALTER TABLE statement seems to be what I need, but I know MySql is very unforgiving so I wanted to get some confirmation from an expert before pressing enter...

So, given that my table is 'comments' and the field is 'date' I would want to do:

Code:

mysql> ALTER TABLE comments MODIFY date VARCHAR(45);

Is this right?

Is that a trick question???

bulliver · 08-15-2006, 06:42 PM

Quote:

Is that a trick question???

What are you talking about? I want to know if that command will fix the field or not. Where's the trick? Trust me, 3000 posts doesn't mean sh*t. It certainly doesn't make me an expert on MySql.

And in the future please don't post a response like this. Now the auto-bump feature will not work and this thread may well roll off into the sunset unanswered...

bulliver · 08-15-2006, 08:09 PM

Ah well sod it, I just went ahead and did it and it worked fine:

Code:

mysql> ALTER TABLE comments MODIFY date VARCHAR(45);
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> desc comments;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| id      | mediumint(10) |      | PRI | NULL    | auto_increment |
| name    | varchar(30)   | YES  |     | NULL    |                |
| email   | varchar(30)   | YES  |     | NULL    |                |
| url     | varchar(30)   | YES  |     | NULL    |                |
| postid  | mediumint(10) | YES  |     | NULL    |                |
| message | text          | YES  |     | NULL    |                |
| date    | varchar(45)   | YES  |     | NULL    |                |
+---------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

penguintutor · 08-16-2006, 05:13 AM

A trick to find out how to modify information in mysql is to use phpmyadmin, which provides a web front end.

You can make the change in there and it will tell you the SQL that it called to make the change. If you want to know how to do it so that you can include it in a script / program then just run it against a test DB and you can then copy and paste the appropriate syntax into your script.

e.g:

Quote:

Table testtable has been altered.
SQL query:
ALTER TABLE `comments` CHANGE `date` `date` VARCHAR( 45 ) NOT NULL

Which is another way of achieving the same thing as your sql.

bulliver · 08-16-2006, 05:22 AM

Quote:

How to get Mysql info without reading the manual

Heh, I did read the manual.

I will admit my hesitancy was probably unfounded but as I said, MySql can be unforgiving if you do something dumb. I really just wanted some confirmation that my query wouldn't, say, drop all the current contents of the field whilst updating or whatever.

Thanks for tip on phpmyadmin. I don't really want it on my live server, but I will install on my workstation and have a play with it...I do really want to learn SQL though....

penguintutor · 08-16-2006, 06:12 AM

Quote:

Heh, I did read the manual.

Sorry I didn't mean to imply you hadn't. It's obvious that you had from your first post.

I meant to say when I don't want to bother reading the manual! The manual is quite well written, but as it describes each part of the sql seperately, it is a pain to try and remember the whole syntax.

I don't write much SQL (just when creating / updating some perl / PHP program) and whilst I used to work it out by hand using the manual I now find it easier to let phpmyadmin generate the SQL rather than spending a lot of time working it out, and then trying to verify what I've written in case it breaks something.

If you don't want to install phpmyadmin on the actual server I think you can install it on another server, but point it to your server, or you could use the Mysql query browser, which can do something similar and work remotely. I use it over a ssh tunnel so that I can connect as a local user and I'm not opening up my mysql over the network.

Just some suggestions - didn't want to suggest you hadn't already done on the groundwork before you posted.