LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
Search this Thread
Old 08-15-2006, 04:27 PM   #1
bulliver
Senior Member
 
Registered: Nov 2002
Location: Edmonton AB, Canada
Distribution: Gentoo x86_64; Gentoo PPC; FreeBSD; OS X 10.9.4
Posts: 3,743
Blog Entries: 4

Rep: Reputation: 76
MySql ALTER TABLE syntax


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?

Last edited by bulliver; 08-15-2006 at 04:29 PM.
 
Old 08-15-2006, 06:33 PM   #2
cxel91a
Member
 
Registered: May 2001
Posts: 61

Rep: Reputation: 15
3000+ posts

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???
 
Old 08-15-2006, 06:42 PM   #3
bulliver
Senior Member
 
Registered: Nov 2002
Location: Edmonton AB, Canada
Distribution: Gentoo x86_64; Gentoo PPC; FreeBSD; OS X 10.9.4
Posts: 3,743
Blog Entries: 4

Original Poster
Rep: Reputation: 76
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...

Last edited by bulliver; 08-15-2006 at 06:46 PM.
 
Old 08-15-2006, 08:09 PM   #4
bulliver
Senior Member
 
Registered: Nov 2002
Location: Edmonton AB, Canada
Distribution: Gentoo x86_64; Gentoo PPC; FreeBSD; OS X 10.9.4
Posts: 3,743
Blog Entries: 4

Original Poster
Rep: Reputation: 76
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)
 
Old 08-16-2006, 05:13 AM   #5
penguintutor
Member
 
Registered: Jun 2006
Location: UK
Distribution: Ubuntu, Mandriva, Redhat and Fedora
Posts: 118

Rep: Reputation: 15
How to get Mysql info without reading the manual

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.
 
Old 08-16-2006, 05:22 AM   #6
bulliver
Senior Member
 
Registered: Nov 2002
Location: Edmonton AB, Canada
Distribution: Gentoo x86_64; Gentoo PPC; FreeBSD; OS X 10.9.4
Posts: 3,743
Blog Entries: 4

Original Poster
Rep: Reputation: 76
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....
 
Old 08-16-2006, 06:12 AM   #7
penguintutor
Member
 
Registered: Jun 2006
Location: UK
Distribution: Ubuntu, Mandriva, Redhat and Fedora
Posts: 118

Rep: Reputation: 15
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.
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Mysql Cluster 5.0 ALTER TABLE In-Depth LXer Syndicated Linux News 0 07-08-2006 10:21 AM
How to alter table with no check option minil Programming 0 08-05-2005 08:11 AM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM
deleted mysql table in mysql now cant do anything nakkaya Linux - Software 0 03-18-2003 06:03 PM


All times are GMT -5. The time now is 07:52 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration