MySQL批量插入从CSV数据文件 [英] MySQL bulk insert from CSV data files
问题描述
我有一些CSV数据文件，我想导入到mySQL。我想在shell脚本中插入，以便它可以自动化。但是，我对在脚本中以明文形式输入用户名和密码有点厌倦。
I have some CSV data files that I want to import into mySQL. I would like to do the insert in a shell script, so that it can be automated. However, I am a bit weary of having the username and password in clear text in the script
我有以下问题：
-
我对于在脚本中使用明文形式的uname / pwd的想法感到不舒服（在这方面有反正，或者我太偏执了）。也许我可以设置一个用户只有INSERT privelege要插入的表？
I am uncomfortable with the idea of a uname/pwd in clear text in the script (is there anyway around this, or am I being too paranoid)?. Maybe I can set up a user with only INSERT privelege for the table to be inserted?
数据库表（导入原始数据）具有基于表列的唯一键。也有可能在我想要导入的数据中有重复。而不是mySQL barfing（即整个插入失败），我想要能够告诉mySQL到 EITHER
The database table (into which the raw data is imported) has a unique key based on the table columns. It is also possible that there may be duplicates in the data that I am trying to import. Rather than mySQL barfing (i.e. the entire insertion fails), I would instead want to be able to tell mySQL to EITHER
（a）使用新数据更新行或
（b）忽略重复行。
(a) UPDATE the row with the new data OR (b) IGNORE the duplicate row.
我选择的任何设置将用于ENTIRE导入，而不是逐行。有任何标志等我可以传递到mysql为了它的行为像（a）OR（b）上面
Whichever setting I choose would be for the ENTIRE import and not on a row by row basis. Are there any flags etc I can pass to mysql in order for it to behave like (a) OR (b) above
- 任何人都可以建议如何编写这样一个（bourne）shell脚本的起点。
推荐答案
您应该阅读有关 mysqlimport 的信息，这是一个命令-line工具。此工具是批量加载CSV数据的最快方式。
You should read about mysqlimport, which is a command-line tool provided with MySQL. This tool is the fastest way to bulk-load CSV data.
此工具有两个选项， - 替换和 - 忽略来处理重复的键冲突。
The tool has two options, --replace and --ignore to handle duplicate key conflicts.
关于安全性，避免在脚本中以纯文本格式输入密码，您还可以使用 MYSQL_PWD 环境变量或 .my.cnf 文件（确保文件是模式400或600）。请参见密码安全最终用户指南。
Regarding security and avoiding putting the password in plain text in the script, you can also use the MYSQL_PWD environment variable or the .my.cnf file (make sure that file is mode 400 or 600). See End-User Guidelines for Password Security.
这篇关于MySQL批量插入从CSV数据文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
