MYSQL to SQLite 脚本

最新推荐文章于 2024-08-16 08:42:34 发布

xak

最新推荐文章于 2024-08-16 08:42:34 发布

阅读量887

点赞数 1

分类专栏：服务器开发文章标签： MYSQL SQLITE 转换

本文链接：https://blog.csdn.net/cubesky/article/details/38754637

版权

服务器开发专栏收录该内容

17 篇文章 0 订阅

订阅专栏

#!/bin/sh
 
#Converts a mysqldump file into a Sqlite 3 compatible file. It alsoextracts the MySQL `KEY xxxxx` from the
#CREATE block and create them in separate commands _after_ all theINSERTs.
 
#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 | sqlite3database.sqlite
#Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWordmyDbase | sqlite3 database.sqlite
 
#Thanks to and @artemyk and @gkuenning for their nicetweaks.
 
mysqldump --compatible=ansi --skip-extended-insert --compact"$@" | \
 
awk '
 
BEGIN {
FS=",$"
print "PRAGMAsynchronous = OFF;"
print "PRAGMAjournal_mode = MEMORY;"
print "BEGINTRANSACTION;"
}
 
# CREATE TRIGGERstatements have funny commenting. Remember we are intrigger.
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/,"CREATE TRIGGER" )
print
inTrigger =1
next
}
 
# The end of CREATETRIGGER has a stray comment terminator
/END \*\/;;/ { gsub(/\*\//, "" ); print; inTrigger = 0; next }
 
# The rest of triggersjust get passed through
inTrigger != 0 { print;next }
 
# Skip othercomments
/^\/\*/ { next}
 
# Print all `INSERT`lines. The single quotes are protected by another singlequote.
/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 `FULLTEXTKEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^ [^"]+KEY/&& !/^ PRIMARY KEY/ { gsub(/.+KEY/, " KEY" ) }
 
# Get rid of fieldlengths in KEY lines
/ KEY/ {gsub(/\([0-9]+\)/, "") }
 
# Print all fieldsdefinition lines except the `KEY` lines.
/^ /&& !/^( KEY|\);)/ {
gsub(/AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTERSET|character set) [^ ]+ /, "" )
gsub( /DEFAULTCURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|defaultcurrent_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 areextracted from the `CREATE` block and stored in array for laterprint
# in a separate `CREATEKEY` command. The index name is prefixed by the table nameto
# avoid a sqlite errorfor duplicate index name.
/^( KEY|\);)/{
if (prev) printprev
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] "CREATEINDEX \"" tableName "_" indexName "\" ON \"" tableName "\" ("indexKey ");\n"
}
}
 
# Print all `KEY`creation lines.
END {
for (table in key)printf key[table]
print "ENDTRANSACTION;"
}
'
exit 0

注：下面的代码来自 http://www.sqlite.org/cvstrac/wiki?p=ConverterTools ，用于转换MySql语句到SQLite语句。

 cat mysql.sql |

 grep -v ' KEY "' |

 grep -v ' UNIQUE KEY "' |

 grep -v ' PRIMARY KEY ' |

 sed 's/ unsigned / /g' |

 sed 's/ auto_increment/ primary key autoincrement/gi' |

 sed 's/ smallint([0-9]*) / integer /gi' |

 sed 's/ tinyint([0-9]*) / integer /gi' |

 sed 's/ int([0-9]*) / integer /gi' |

 sed 's/ character set [^ ]* / /gi' |

 sed 's/ enum([^)]*) / varchar(255) /gi' |

 sed 's/ on update [^,]*//gi' |

 perl -e 'local$/;$_=<>;s/,\n\)/\n\)/gs;print"begin;\n";print;print "commit;\n"' |

 perl -pe '

  if(/^(INSERT.+?)\(/) {

    $a=$1;

    s/\\'\''/'\'\''/g;

    s/\\n/\n/g;

    s/\),\(/\);\n$a\(/g;

 }