MySQL的CSV引擎应用实例解析

POOPE

　　如果您想把EXCEL的数据或者CSV格式的数据导入到MySQL中，MySQL的CSV引擎再适合不过了。
MySQL的CSV引擎在5.0后开始提供，不过不支持WINDOWS，到了5.1才支持。
今天我测试的版本号是5.0.45
一、注意几点：
1、没有索引，跟MySQL5的数据字典库一样。
2、可以直接用任何文本编辑器来编辑数据文件。
3、非英文编码问题。
我的字符终端和表都是UTF-8的，所以要把上传的CSV文件保存为UTF-8的编码。
4、编码转化工具，我这边在WINDOWS下用EDITPLUS来转化，在LINUX下可以用ICONV命令行工具来转化编码。
二、示例数据文件。
"1","Designedfor 99.999% Availability","MySQL Cluster provides a fault tolerantarchitecture that ensures your organization's mission criticalapplications achieve 99.999% availability. This means less than 5minutes downtime per year, including scheduled maintenance time. MySQLCluster implements automatic node recoverability to ensure anapplication automatically fails over to another database node thatcontains a consistent data set, if one or more database nodes fail.Should all nodes fail due to hardware faults for example, MySQL Clusterensures an entire system can be safely recovered in a consistent stateby using a combination of checkpoints and log execution. Furthermore,MySQL Cluster ensures systems are available and consistent acrossgeographies by enabling entire clusters to be replicated acrossregions."
"2","HighPerformance Only a Main Memory Database Can Deliver","MySQL Clusterprovides the response time and throughput to meet the most demandinghigh volume enterprise applications. MySQL Cluster achieves itsperformance advantage by being a main memory clustered databasesolution, which keeps all data in memory and limits IO bottlenecks byasynchronously writing transaction logs to disk. MySQL Cluster alsoenables servers to share processing within a cluster, taking fulladvantage of all hardware. Typical response times for MySQL Cluster arein the range of a few milliseconds and MySQL Cluster has been proven tohandle tens of thousands of distributed transactions per second thatare also replicated across database nodes."
"3","ExtremelyFast Automatic Failover","MySQL delivers extremely fast failover timewith sub-second response so your applications can recover quickly inthe event of application, network or hardware failure. MySQL Clusteruses synchronous replication to propagate transaction information toall the appropriate database nodes so applications can automaticallyfail over to another node extremely quickly. This eliminates the timeconsuming operation of recreating and replaying log files required by'Shared-Disk' architectures to fail over successfully. Plus, MySQLCluster database nodes are able to automatically restart, recover, anddynamically reconfigure themselves in case of failures without havingto program advanced features into the application."
"4","FlexibleDistributed Architecture with No Single Point of Failure","The parallelserver architecture combines database nodes, management server nodes,and application nodes that can be distributed across computers andgeographies to ensure there is no single point of failure. Any node canbe stopped or started without stopping the applications that use thedatabase. And MySQL Cluster is highly configurable so you can implementthe appropriate level of performance, scalability and fault toleranceto match your application requirements."
"5","SignificantlyReduce Costly Downtime","MySQL Cluster not only lowers up-front licensecosts with affordable commercial licensing under a dual licensingmechanism, but it also significantly reduces system downtime - thenumber one contributor to the Total Cost of Ownership (TCO) of databasesoftware. Furthermore, a highly portable standards-based environmentallows you to cost-effectively distribute your applications usingcommodity hardware and open source software infrastructure."
"6","LowerMaintenance Costs","MySQL Cluster is designed to be largelyself-governing so very few system parameters actually need fine-tuning,further reducing the risk of costly errors. As a result, there aretypically fewer conflicts with other software and hardware, and lessneed for manual intervention. This also means that MySQL Cluster willhave a much lower maintenance costs, with less fine tuning required byDatabase Administrators."
"7","Easy-to-useAdministration","MySQL Cluster includes easy to use and powerful toolsfor administering your clustered environment. Command line tools enableyou to monitor database nodes, control access to applications, andcreate and restore backups."
"8","Servicesand Support","MySQL provides extensive consulting, training andtechnical support services to ensure the success of your nextmission-critical database application project. MySQL has a proven trackrecord gained through millions of successful customer deployments thatcan lower your risk and maximize return on investment."
"9","MySQL CLUSTER","以上是集群的特性概括"
"10","月亮他爸","我博客地址：http://yueliangdao0608.cublog.cn"
"11","最要注意的一点","最后的一行必须有个空格！如果是中文，好奇怪！"


三、直接COPY数据文件到MySQL数据库目录下。
1、用EDITPLUS转码，然后进入MySQL命令行。
mysql> \C gbk
Charset changed
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)
mysql> select sql_calc_found_rows * from ytt order by id desc limit 2\G
*************************** 1. row ***************************
     id: 11
  title: 最要注意的一点
summary: 最后的一行必须有个空格！如果是中文，好奇怪！"
*************************** 2. row ***************************
     id: 10
  title: 月亮他爸
summary: 我博客地址：http://yueliangdao0608.cublog.cn"
2 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|           11 |
+--------------+
1 row in set (0.00 sec)
2、用ICONV转码
现在可以直接用EXCEL来编辑数据文件，不过可惜的是EXCEL的编码是CP936的，所以传上去后要用ICONV来转码。

[root@localhost t_girl]# iconv -f CP936 -t UTF-8 ytt.CSV -o ytt.new
[root@localhost t_girl]# chown mysql:mysql ytt.new
[root@localhost t_girl]# cp -uf ytt.new ytt.CSV
[root@localhost t_girl]# ls -sihl
total 56K
13797305 8.0K -rw-rw---- 1 mysql mysql   61 Apr 30 15:19 db.opt
30539777 8.0K -rw-rw---- 1 mysql mysql  383 Jun 19 09:54 show_user.frm
13797308  12K -rw-r--r-- 1 mysql mysql 4.5K Aug  9 14:59 ytt.CSV
13797306  16K -rw-rw---- 1 mysql mysql 8.5K Aug  8 17:39 ytt.frm
13797307  12K -rw-r--r-- 1 mysql mysql 4.5K Aug  9 14:57 ytt.new
[root@localhost t_girl]# sed -i 's/^/"/g' ytt.CSV
[root@localhost t_girl]# sed -i 's/,/","/g' ytt.CSV
[root@localhost t_girl]# sed -i 's/$/"/g' ytt.CSV

用SED替换是为了用双引号来分割各个字段。