mysql2sqlite.sh Auto_Increment [英] mysql2sqlite.sh Auto_Increment
问题描述
原始MySQl Tbl_driver
original MySQl Tbl_driver
delimiter $$
CREATE TABLE `tbl_driver` (
`_id` int(11) NOT NULL AUTO_INCREMENT,
`Driver_Code` varchar(45) NOT NULL,
`Driver_Name` varchar(45) NOT NULL,
`AddBy_ID` int(11) NOT NULL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1$$
mysql2sqlite.sh
#!/bin/sh
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \
awk '
BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
# CREATE TRIGGER statements have funny commenting. Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }
# Skip other comments
/^\/\*/ { next }
# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}
# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
print
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }
# Print all fields definition lines except the `KEY` lines.
/^ / && !/^( KEY|\);)/ {
gsub( /AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\)/, "text ")
gsub(/(SET|set)\([^)]+\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
if (prev) print prev ","
prev = $1
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
# avoid a sqlite error for duplicate index name.
/^( KEY|\);)/ {
if (prev) print prev
prev=""
if ($0 == ");"){
print
} else {
if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}
}
# Print all `KEY` creation lines.
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
'
exit 0
执行此脚本时，我的sqlite数据库变成这样
when execute this script, my sqlite database become like this
Sqlite Tbl_Driver
Sqlite Tbl_Driver
CREATE TABLE "tbl_driver" (
"_id" int(11) NOT NULL ,
"Driver_Code" varchar(45) NOT NULL,
"Driver_Name" varchar(45) NOT NULL,
"AddBy_ID" int(11) NOT NULL,
PRIMARY KEY ("_id")
)
我要更改"_id" int(11) NOT NULL ,
变得像这样"_id" int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
或
变得像这样"_id" int(11) NOT NULL AUTO_INCREMENT,
没有主键也可以
i want to change "_id" int(11) NOT NULL ,
become like this "_id" int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
or
become like this "_id" int(11) NOT NULL AUTO_INCREMENT,
with out primary key also can
有任何修改此脚本的想法吗?
any idea to modify this script?
推荐答案
AUTO_INCREMENT关键字特定于MySQL.
The AUTO_INCREMENT keyword is specific to MySQL.
SQLite的关键字为AUTOINCREMENT(不带下划线)，这意味着该列自动生成单调递增的值，而该值从未在表中使用过.
SQLite has a keyword AUTOINCREMENT (without the underscore) which means the column auto-generates monotonically increasing values that have never been used before in the table.
如果省略了AUTOINCREMENT关键字(如您当前显示的脚本一样)，SQLite会将ROWID分配给新行，这意味着它将比表中当前的最大ROWID值大1.如果您从表的高端删除行，然后插入新行，则可以重新使用值.
If you leave out the AUTOINCREMENT keyword (as the script you show does currently), SQLite assigns the ROWID to a new row, which means it will be a value 1 greater than the current greatest ROWID in the table. This could re-use values if you delete rows from the high end of the table and then insert new rows.
有关更多详细信息，请参见 http://www.sqlite.org/autoinc.html .
See http://www.sqlite.org/autoinc.html for more details.
如果要修改此脚本以添加AUTOINCREMENT关键字，则看起来可以修改以下行:
If you want to modify this script to add the AUTOINCREMENT keyword, it looks like you could modify this line:
gsub( /AUTO_INCREMENT|auto_increment/, "" )
对此:
gsub( /AUTO_INCREMENT|auto_increment/, "AUTOINCREMENT" )
发表您的评论
Re your comments:
好的，我在使用sqlite3的虚拟表上尝试过.
Okay I tried it on a dummy table using sqlite3.
sqlite> create table foo (
i int autoincrement,
primary key (i)
);
Error: near "autoincrement": syntax error
显然，SQLite要求autoincrement遵循列级主键约束.对于MySQL约定，将pk约束放在表级约束的最后，这是不满意的. SQLite CREATE TABLE的文档中的语法图对此提供了支持.
Apparently SQLite requires that autoincrement follow a column-level primary key constraint. It's not happy with the MySQL convention of putting the pk constraint at the end, as a table-level constraint. That's supported by the syntax diagrams in the SQLite documentation for CREATE TABLE.
让我们尝试将primary key放在autoincrement之前.
Let's try putting primary key before autoincrement.
sqlite> create table foo (
i int primary key autoincrement
);
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
显然SQLite不喜欢"INT"，它更喜欢"INTEGER":
And apparently SQLite doesn't like "INT", it prefers "INTEGER":
sqlite> create table foo (
i integer primary key autoincrement
);
sqlite>
成功！
因此，您的awk脚本无法像您想象的那样轻松地将MySQL表DDL转换为SQLite.
So your awk script is not able to translate MySQL table DDL into SQLite as easily as you thought it would.
发表您的评论
您正在尝试复制称为 SQL :: Translator ，这是很多工作.我不会为您编写完整的工作脚本.
You're trying to duplicate the work of a Perl module called SQL::Translator, which is a lot of work. I'm not going to write a full working script for you.
要真正解决此问题，并使脚本能够自动进行所有语法更改以使DDL与SQLite兼容，则需要为SQL DDL实现完整的解析器.这在awk中不切实际.
To really solve this, and make a script that can automate all syntax changes to make the DDL compatible with SQLite, you would need to implement a full parser for SQL DDL. This is not practical to do in awk.
我建议您在某些关键字替换情况下使用脚本，然后如果需要进一步更改，请在文本编辑器中手动进行修复.
I recommend that you use your script for some of the cases of keyword substitution, and then if further changes are necessary, fix them by hand in a text editor.
也可以考虑做出让步.如果在SQLite中重新格式化DDL以使用AUTOINCREMENT功能太困难了，请考虑一下默认的ROWID功能是否足够接近.阅读我上面发布的链接以了解不同之处.
Also consider making compromises. If it's too difficult to reformat the DDL to use the AUTOINCREMENT feature in SQLite, consider if the default ROWID functionality is close enough. Read the link I posted above to understand the differences.
这篇关于mysql2sqlite.sh Auto_Increment的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
