MySQL changing column types Oct27 '06

Often in your database development work, you'll come across a situation where a particular column type no longer "fits" the type of data that needs to be stored in it.

For example, let's say you're storing dollar amounts in an integer field, which can't hold decimal values.

Later on, someone decides they need to store decimal values in that field, but can't.

You need to convert that integer field to a float field, but you don't want to touch any existing data in that field. You want the existing data to remain the way it is, and only start accommodating decimal values.

Rather than just changing the field type directly, and hoping that no data is lost or converted to something else - the best (and safest) way to accomplish this is to create a new field, and copy the old values over.

UPDATE table SET newField = primaryField;

This query copies all of the values from primaryField to newField.

Then, remove primaryField entirely, and rename newField to primaryField.

Sometimes it helps to only rename primaryField to something like primaryField_old, in case it needs to be restored immediately, for whatever reason.

Categories: MySQL

Add Feedback (view all)

Leave feedback

Feedback

Input format: The editor controls below will assist with Markdown syntax.

Status

Sub-status

Your info

Dude, are you going to send me your resume or what? :) ... Read more.

matthom is published and produced by Matt Thommes - an independent publishing enthusiast, mobile blogger, content creator, informative writer, web developer from a suburb of Chicago. Never one to conform, Matt intends to promote the effect the web has on our lives, in an effort to intensify, instruct, and clarify all that is happening around us.

Contact Matt

Popular Pages

  1. Fast rounded corners in Photoshop (3954 recent visits)
  2. PHP – passing variables across pages (1485 recent visits)
  3. JavaScript set selected on load (1209 recent visits)
  4. Removing all child nodes from an element (827 recent visits)
  5. iPod songs out of order? (720 recent visits)
  6. Britney - Everytime piano tab (649 recent visits)
  7. Firefox 3 smart address bar: wildcard search (607 recent visits)
  8. MySQL LEFT JOIN syntax (513 recent visits)
  9. Breathe Me - Sia (501 recent visits)
  10. Tumblr: how blogging should be (384 recent visits)

Similar Entries

Stats

89 unique visits since August 2008

Syndicate

Advertisements