mysql - 应如何使用 Scriptella 将带有 NULL 值的 CSV 加载到 MySQL 中？

Question

我有一个 CSV 文件，正在尝试加载到 MySQL 表中:

bkpgln,date,aircraft,tailnum,poe,time_out,pod,time_in,stops,inst_app,landings,nitelndgs,nav,se_retrgear,a_sel,a_mel,xc,day,night,act_inst,sim_inst,simulator,dualrecd,pic,p2,duration,remarks
054801,2013-08-19,C182,N12345,IPT,17:12:00,UNV,17:54:00,,,,,0.7,,,,,,,,,,,,,,CAP 13-1-9999/A3/121 John Doe. CREW P/U (me).
[...more lines like this...]

我的加载脚本似乎失败了，因为它不接受整数列的 NULL(空？)值，即使在表定义中允许 NULL。这是我可以轻松解决的问题吗？

$ ./load-csv-with-scriptella
Dec 20, 2013 3:01:31 PM <WARNING> XML configuration warning in file:/Users/gknauth/test/db-rebuild/etl.xml(3:79): Attribute "encoding" must be declared for element type "connection".
Dec 20, 2013 3:01:31 PM <WARNING> XML configuration warning in file:/Users/gknauth/test/db-rebuild/etl.xml(5:128): Attribute "encoding" must be declared for element type "connection".
Dec 20, 2013 3:01:31 PM <INFO> Execution Progress.Initializing properties: 1%
Dec 20, 2013 3:01:31 PM <INFO> Execution Progress.Initialized connection id=in, CsvConnection, Dialect{CSV 1.0}, properties {}: 3%
Dec 20, 2013 3:01:31 PM <INFO> Execution Progress.Initialized connection id=out, JdbcConnection{com.mysql.jdbc.JDBC4Connection}, Dialect{MySQL 5.1.72}, properties {}: 5%
Dec 20, 2013 3:01:31 PM <INFO> Execution Progress./etl/query[1] prepared: 10%
Dec 20, 2013 3:01:31 PM <INFO> Registered JMX mbean: scriptella:type=etl,url="file:/Users/gknauth/test/db-rebuild/etl.xml"
Dec 20, 2013 3:01:31 PM <WARNING> Unable to rollback transaction for connection CsvConnection: Transactions are not supported by CsvConnection
Dec 20, 2013 3:01:31 PM <SEVERE> Script /Users/gknauth/test/db-rebuild/etl.xml execution failed.
Location: /etl/query[1]/script[1]
JDBC provider exception: Unable to execute statement
Error statement: 
INSERT INTO logbook
(bkpgln,date,aircraft,tailnum,poe,time_out,pod,time_in,stops,inst_app,landings,nitelndgs,nav,se_retrgear,a_sel,a_mel,xc,day,night,act_inst,sim_inst,simulator,dualrecd,pic,p2,duration,remarks)
VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
. Parameters: [054801, 2013-08-19, C182, N12345, IPT, 17:12:00, UNV, 17:54:00, , , , , 0.7, , , , , , , , , , , , , , CAP 13-1-9999/A3/121 John Doe. CREW P/U (me).]
Error codes: [HY000, 1366]
Driver exception: java.sql.SQLException: Incorrect integer value: '' for column 'inst_app' at row 1

这是我的 etl.xml:

<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
  <connection id="in" driver="csv" url="logbook-update.csv" encoding="UTF-8"/>
  <connection id="out" driver="mysql" url="jdbc:mysql://localhost/flying" 
      classpath="/Users/gknauth/lib/jar/mysql-connector-java-5.1.23-bin.jar" user="username" password="password" encoding="UTF-8"/>
  <query connection-id="in">
    <script connection-id="out">
      INSERT INTO logbook
(bkpgln,date,aircraft,tailnum,poe,time_out,pod,time_in,stops,inst_app,landings,nitelndgs,nav,se_retrgear,a_sel,a_mel,xc,day,night,act_inst,sim_inst,simulator,dualrecd,pic,p2,duration,remarks)
      VALUES
      (?bkpgln,?date,?aircraft,?tailnum,?poe,?time_out,?pod,?time_in,?stops,?inst_app,?landings,?nitelndgs,?nav,?se_retrgear,?a_sel,?a_mel,?xc,?day,?night,?act_inst,?sim_inst,?simulator,?dualrecd,?pic,?p2,?duration,?remarks)
    </script>
  </query>
</etl>

Answer 1

我自己找到了问题的答案。这是我的修复:

$ diff etl.xml{~,}
3c3,5
<   <connection id="in" driver="csv" url="logbook-update.csv" encoding="UTF-8"/>
---
>   <connection id="in" driver="csv" url="logbook-update.csv" encoding="UTF-8">
>     null_string=
>     </connection>