Analysis of MySQL CSV Engine Application Instances

If you want to import EXCEL or CSV data to MySQL, MySQL's CSV engine is no longer suitable.

MySQL CSV engine is provided after 5.0, but does not support WINDOWS. It is only supported by 5.1.

The version I tested today is 5.0.45.

I. Notes:
1. No index. It is the same as the data dictionary library of mysql5.
2. You can directly use any text editor to edit data files.
3. Non-English encoding.

My character terminals and tables are both UTF-8, so save the uploaded CSV file as the encoding of the UTF-8.

4. encoding conversion tool. In WINDOWS, EDITPLUS is used for conversion. in LINUX, ICONV command line tool can be used for conversion.

Ii. sample data file.
"1", "Designed for 99.999% Availability", "MySQL Cluster provides a fault tolerant architecture that ensures your organization's mission critical applications achieve 99.999% availability. this means less than 5 minutes downtime per year, including scheduled maintenance time. mySQL Cluster implements automatic node recoverability to ensure an application automatically fails over to another database node that contains a consistent data set, if one or more database nodes fail. shocould all nodes fail due to hardware faults for example, MySQL Cluster ensures an entire system can be safely recovered in a consistent state by using a combination of checkpoints and log execution. furthermore, MySQL Cluster ensures systems are available and consistent parent SS geographies by enabling entire clusters to be replicated against SS regions."
"2", "High Performance Only a Main Memory Database Can Deliver", "MySQL Cluster provides the response time and throughput to meet the most demaning high volume enterprise applications. mySQL Cluster achieves its performance advantage by being a main memory clustered database solution, which keeps all data in memory and limits IO bottlenecks by asynchronously writing transaction logs to disk. mySQL Cluster also enables servers to share processing within a cluster, taking full advantage of all hardware. typical response times for MySQL Cluster are in the range of a few milliseconds and MySQL Cluster has been proven to handle tens of thousands of distributed transactions per second that are also replicated against SS database nodes."
"3", "Extremely Fast Automatic Failover", "MySQL delivers extremely fast failover time with sub-second response so your applications can recover quickly in the event of application, network or hardware failure. mySQL Cluster uses synchronous replication to propagate transaction information to all the appropriate database nodes so applications can be automatically fail over to another node extremely quickly. this eliminates the time consuming operation of recreating and replaying log files required by 'shared-disk' ubuntures to fail over successfully. plus, MySQL Cluster database nodes are able to automatically restart, recover, and dynamically reconfigure themselves in case of failures without having to program advanced features into the application."
"4", "Flexible Distributed Architecture with No Single Point of Failure", "The parallel server architecture combines database nodes, management server nodes, and application nodes that can be distributed into SS computers and geographies to ensure there is no single point of failure. any node can be stopped or started without stopping the applications that use the database. and MySQL Cluster is highly writable able so you can implement the appropriate level of performance, scalability and fault tolerance to match your application requirements."
"5", "Significantly Reduce Costly Downtime", "MySQL Cluster not only lowers up-front license costs with affordable implements cial licensing under a dual licensing mechanic, but it also significantly reduces system downtime-the number one contributor to the Total Cost of Ownership (TCO) of database software. furthermore, a highly portable standards-based environment allows you to cost-interval tively distribute your applications using commodity hardware and open source software infrastructure."
"6", "Lower Maintenance Costs", "MySQL Cluster is designed to be largely self-governing so very few system parameters actually need fine-tuning, further won the risk of costly errors. as a result, there are typically fewer conflicts with other software and hardware, and less need for manual intervention. this also means that MySQL Cluster will have a much lower maintenance costs, with less fine tuning required by Database Administrators."
"7", "Easy-to-use Administration", "MySQL Cluster primary des easy to use and powerful tools for administering your clustered environment. command line tools enable you to monitor database nodes, control access to applications, and create and restore backups."
"8", "Services and Support", "MySQL provides extensive consulting, training and technical support services to ensure the success of your next mission-critical database application project. mySQL has a proven track record gained through millions of successful customer deployments that can lower your risk and maximize return on investment."
"9", "MySQL CLUSTER", "The above is a summary of CLUSTER Features"
"10", "Moon dad ","
"11", "the most important thing to note", "the last line must have a space! It's strange if it's Chinese! "

3. COPY the data file directly to the MySQL database directory.

1. Use EDITPLUS for transcoding and then enter the MySQL command line.
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: The most important point
Summary: The last line must have a space! It's strange if it's Chinese! "
* *************************** 2. row ***************************
Id: 10
Title: Moon Dad
Summary:

2 rows in set (0.00 sec)

Mysql> select found_rows ();
+ -------------- +
| Found_rows () |
+ -------------- +
| 11 |
+ -------------- +
1 row in set (0.00 sec)

2. Use ICONV for Transcoding
Now you can directly use EXCEL to edit data files. Unfortunately, the EXCEL Code is CP936, so ICONV is used for transcoding after being uploaded.

[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 56 K
13797305 8.0 K-rw ---- 1 mysql 61 Apr 30 db. opt
30539777 8.0 K-rw ---- 1 mysql 383 Jun 19 show_user.frm
13797308 12 K-rw-r -- 1 mysql 4.5 K Aug 9 ytt. CSV
13797306 16 K-rw ---- 1 mysql 8.5 K Aug 8 ytt. frm
13797307 12 K-rw-r -- 1 mysql 4.5 K Aug 9 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 is used to separate fields with double quotation marks.