如何将 MDB (Access)文件转换为 MySQL (或普通 SQL 文件) ？

是否可以从 MicrosoftAccess 数据库创建 SQL 命令转储？我希望将这个 MDB 文件转换成 MySQL 数据库以便导入，这样我就不必经历 CSV 步骤。

我希望甚至一个 MSSQL 转储文件仍然包含可用的 SQL 命令，但我对 MSSQL 一无所知，请让我知道。

177190 次浏览
小开

You want to convert mdb to mysql (direct transfer to mysql or mysql dump)?

Try a software called Access to MySQL.

Access to MySQL is a small program that will convert Microsoft Access Databases to MySQL.

  • Wizard interface.
  • Transfer data directly from one server to another.
  • Create a dump file.
  • Select tables to transfer.
  • Select fields to transfer.
  • Transfer password protected databases.
  • Supports both shared security and user-level security.
  • Optional transfer of indexes.
  • Optional transfer of records.
  • Optional transfer of default values in field definitions.
  • Identifies and transfers auto number field types.
  • Command line interface.
  • Easy install, uninstall and upgrade.

See the aforementioned link for a step-by-step tutorial with screenshots.

小开

If you have access to a linux box with mdbtools installed, you can use this Bash shell script (save as mdbconvert.sh):

#!/bin/bash


TABLES=$(mdb-tables -1 $1)


MUSER="root"
MPASS="yourpassword"
MDB="$2"


MYSQL=$(which mysql)


for t in $TABLES
do
$MYSQL -u $MUSER -p$MPASS $MDB -e "DROP TABLE IF EXISTS $t"
done


mdb-schema $1 mysql | $MYSQL -u $MUSER -p$MPASS $MDB


for t in $TABLES
do
mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t | $MYSQL -u $MUSER -p$MPASS $MDB
done

To invoke it simply call it like this:

./mdbconvert.sh accessfile.mdb mysqldatabasename

It will import all tables and all data.

小开

I modified the script by Nicolay77 to output the database to stdout (the usual way of unix scripts) so that I could output the data to text file or pipe it to any program I want. The resulting script is a bit simpler and works well.

Some examples:

./mdb_to_mysql.sh database.mdb > data.sql


./mdb_to_mysql.sh database.mdb | mysql destination-db -u user -p

Here is the modified script (save to mdb_to_mysql.sh)

#!/bin/bash
TABLES=$(mdb-tables -1 $1)


for t in $TABLES
do
echo "DROP TABLE IF EXISTS $t;"
done


mdb-schema $1 mysql


for t in $TABLES
do
mdb-export -D '%Y-%m-%d %H:%M:%S' -I mysql $1 $t
done
小开

OSX users can follow by Nicolay77 or mikkom that uses the mdbtools utility. You can install it via Homebrew. Just have your homebrew installed and then go

$ brew install mdbtools

Then create one of the scripts described by the guys and use it. I've used mikkom's one, converted all my mdb files into sql.

$ ./to_mysql.sh myfile.mdb > myfile.sql

(which btw contains more than 1 table)


提交答案