change a field and port mysql table data via scrip

问题:

mysql> desc oldtable;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| uid           | int(11)      | NO   | PRI | NULL    | auto_increment |
| active        | char(1)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+


mysql> desc newtable;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| uid        | int(11)      | NO   | PRI | NULL    | auto_increment |
| active     | tinyint(1)   | NO   |     | 0       |                |
+------------+--------------+------+-----+---------+----------------+

I would like to port data (dump) from oldtable into newtable. One issue is, earlier the table used char(1) for active which stores value either 'Y' or 'N'. Now the newtable stores it as int either 1 or 0.

How can i fix this before porting data? Should I use shell script for such fix & porting ? Any sample scripts or tips :)

回答1:

INSERT INTO newtable 
SELECT uid,IF(active='Y',1,0) as active FROM oldtable

should do the trick

回答2:

INSERT INTO newtable (uid, active)
    SELECT uid, IF(active='Y', 1, 0) AS active
    FROM oldtable

docs for this syntax here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html

回答3:

Joking version:

INSERT INTO newtable 
SELECT uid,FIELD(active,'Y') as active 
FROM oldtable