通过脚本改变字段和端口的MySQL表中的数据？ [英] change a field and port mysql table data via script ?

问题描述

  MySQL的＆GT;说明oldtable;
+ --------------- + -------------- + ------ + ----- + ----- ---- + ---------------- +
|现场|类型|空|钥匙|默认|额外|
+ --------------- + -------------- + ------ + ----- + ----- ---- + ---------------- +
| UID | INT（11）| NO | PRI | NULL | AUTO_INCREMENT |
|主动| CHAR（1）| NO | | NULL | |
+ --------------- + -------------- + ------ + ----- + ----- ---- + ---------------- +
MySQL的＆GT;说明newtable的;
+ ------------ + -------------- + ------ + ----- + -------- -  + ---------------- +
|现场|类型|空|钥匙|默认|额外|
+ ------------ + -------------- + ------ + ----- + -------- -  + ---------------- +
| UID | INT（11）| NO | PRI | NULL | AUTO_INCREMENT |
|主动| TINYINT（1）| NO | | 0 | |
+ ------------ + -------------- + ------ + ----- + -------- -  + ---------------- +
 

我想从港口数据oldtable（转储）到newtable中。一个问题是，先前活跃的表中使用CHAR（1），其存储值是'Y'或'N'。现在newtable中把它存储为int 1或0。

如何移植数据之前解决这一问题？我应该使用这样的修复与放大器的shell脚本;移植？
任何示例脚本或提示:)

解决方案

  INSERT INTO newtable的
SELECT UID，IF（主动='Y'，1,0）FROM oldtable为活动
 

应该做的伎俩

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 :)

解决方案

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

should do the trick

这篇关于通过脚本改变字段和端口的MySQL表中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！