sql server 导出的datetime结果 CAST(0x00009E0E0095524F AS DateTime) 如何向mysql,oracle等数据库进行转换

时间：2022-07-20 01:38:54

1. 处理 sql server 导出的 datetime 类型的字段

在进行sql server向mysql等其他数据进行迁移数据时，会发现使用sql server导出的datetime类型的结果是16进制表示的二进制的结果，类似于：CAST(0x00009E0E0095524F AS DateTime)，这样形式的datetime是无法向其他数据库插入的，所以需要将这种表现形式进行转换。搜索了很久，才在在*上找到正确的转换方法。在网上看到很多人都这个问题都不知道解决办法，本文采用Java语言根据*介绍的原理，进行编码实现转换。

注意，因为datetime分为了4字节形式(SmallDateTime)和8字节形式(DateTime)，所有对应的又两种转换方法。另外本转义方法，只能精确到秒级别，毫秒级别是不精确的。如果需要完全精确的转换，请采用其他工具进行转换，比如sqlyog, mysql workbench等工具。

下面是具体的代码：

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.nio.charset.StandardCharsets;

import java.nio.file.Files;

import java.nio.file.Paths;

import java.text.SimpleDateFormat;

import java.util.Calendar;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

/**

 * sql server数据库在向mysql等数据库迁移数据时，会遇到一个麻烦，sql server导出的datetime的结果是16进制形式的二进制结果，

 * 类似于 CAST(0x00009E0E0095524F AS DateTime)，这样的导出结果是无法直接向mysql数据库中导入的，所以需要对sql server

 * 导出的脚本中的所有的 datetime 字段类型进行转换，转换成mysql等数据库认可的结果：2010-10-13 09:03:39

 * 才能正确的完成sql server数据向mysql等数据库的迁移。

 * 注意本方法只能精确到秒级别，毫秒级别是不精确的。

 * 具体转换原理，参见：http://*.com/questions/12033598/cast-hex-as-datatime-how-to-get-date

 * @author digdeep@126.com

 */

public class SqlServerDateTimeUtils {

    private static final Calendar cal = Calendar.getInstance();

    static{

        cal.set(Calendar.YEAR, 1900);

        cal.set(Calendar.MONTH, Calendar.JANUARY);

        cal.set(Calendar.DATE, 1);

        cal.set(Calendar.HOUR_OF_DAY, 0);

        cal.set(Calendar.MINUTE, 0);

        cal.set(Calendar.SECOND, 0);    // 1900-01-01 00:00:00

    }

    /**

     * 将sql server导出的16进制datetime字段： CAST(0x00009E0E0095524F AS DateTime) 形式转换成字符串形式：

     * 2010-10-13 09:03:39 ，以利于 sql server 向 mysql, oracle 迁移数据

     * @param rawDateTime

     * @return

     * reference: http://*.com/questions/12033598/cast-hex-as-datatime-how-to-get-date

     */

    public static String convertRawDateTimeToStr(String rawDateTime){

        return convertRawToStr(rawDateTime, false);

    }

    /**

     * 将sql server导出的datetime字段结果 CAST(0x00009E0E0095524F AS DateTime)，转换成

     * 2010-10-13 09:03:39 或者 2010-10-13 09:03:39.394 注意毫秒部分不精确

     * @param rawDateTime

     * @param millisecondFlag 结果是否需要带毫秒部分

     * @return

     * reference: http://*.com/questions/12033598/cast-hex-as-datatime-how-to-get-date

     */

    public static String convertRawToStr(String rawDateTime, boolean millisecondFlag) {

        if (rawDateTime == null || rawDateTime.trim().equals(""))

            return null;

        String rawData = rawDateTime.substring("CAST(".length(), rawDateTime.toUpperCase().indexOf(" AS DATETIME"));

        if (rawData == null || rawData.trim().equals(""))

            return null;

        rawData = rawData.trim();

        String result = null;

        if(rawData.length() <= 10){        // rowData = "0x993a02CE"

            result = getDateTimeStr4Bytes(rawData);

        }

        if(rawData.length() > 10 && rawData.length() <= 18){    // rowData = "0x00009E0E0095524F"

            result = getDateTimeStr8Bytes(rawData, millisecondFlag);

        }

        return result;

    }

    /**

     * sql server 利用 SmallDateTime 4个字节

     * select CAST(0x993902CE as  SmallDateTime);

     * 2007-05-25 11:58:00 (只精确到分钟？？？)

     *

     * mysql:

     * SELECT "0x993902CE" INTO @raw_data;

     * SELECT conv(substr(@raw_data, 3, 4), 16, 10) INTO @days;

     * SELECT conv(substr(@raw_data, 7, 4), 16, 10) INTO @minutes;

     * SELECT "1900-01-01 00:00:00" INTO @start_date;

     * SELECT date_add(@start_date, interval @days DAY) INTO @date_plus_years;

     * SELECT date_add(@date_plus_years, interval @minutes MINUTE) INTO @final_date;

     * select @final_date;

      * 2007-05-25 11:58:00

     * @param rawData

     * @return

     */

    private static String getDateTimeStr4Bytes(String rawData){

        String day = rawData.substring(2, 2 + 4);    // rowData = "0x993a02CE"

        String minutes = rawData.substring(6, 6 + 4);

        Calendar calendar =  Calendar.getInstance();

        calendar.setTimeInMillis(cal.getTimeInMillis());    // 1900-01-01 00:00:00

        calendar.add(Calendar.DATE, Integer.parseInt(day, 16));

        calendar.add(Calendar.MINUTE, Integer.parseInt(minutes, 16));

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        return sdf.format(calendar.getTime());

    }

    /**

     * sql server DateTime 利用 8 字节表示， 该转换精确到秒，无毫秒部分

     * @param rawData

     * @param millisecondFlag 结果是否需要带毫秒部分

     * @return

     *

     */

    private static String getDateTimeStr8Bytes(String rawData, boolean millisecondFlag){

        String day = rawData.substring(2, 2 + 8);            // 4字节表示距离1900年的天数

        String fraction = rawData.substring(10, 10 + 8);    // 4字节表示剩余的部分 faction

        int millis =  (int)(Integer.parseInt(fraction, 16) * 3.33333);    // faction*3.3333 之后表示的是毫秒数

        int seconds= millis / 1000;    // 得到秒数

        Calendar calendar =  Calendar.getInstance();

        calendar.setTimeInMillis(cal.getTimeInMillis());    // 1900-01-01 00:00:00

        calendar.add(Calendar.DATE, Integer.parseInt(day, 16));    // 加上天数

        calendar.add(Calendar.SECOND,  seconds);    // 加上秒数

//      calendar.add(Calendar.MILLISECOND,  millis); // 采用 Calendar.MILLISECOND 计算会导致在秒级别的出现误差

        SimpleDateFormat sdf = null;

        if(millisecondFlag)

            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");    // 毫秒部分是不精确的，毫秒部分每次运行的结果不相同！

        else

            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");    // 不带毫秒部分，秒级别是精确的，每次运行的结果相同    

        return sdf.format(calendar.getTime());

    }

    public static void main(String[] args) {

        //sql server: select CAST(0x00009E0E0095524F AS DateTime) == 2010-10-13 09:03:39.783  正确的日期值

        String str = "CAST(0x00009E0E0095524F AS DateTime)";

        System.out.println(convertRawToStr(str, true));        // 2010-10-13 09:03:39.374  毫秒部分不精确

        System.out.println(convertRawDateTimeToStr(str));    // 2010-10-13 09:03:39  秒级别是精确的

        try {

            // 这里的字符集一般是 StandardCharsets.UTF_16 或者 StandardCharsets.UTF_8，具体看你导出时采用的是那种字符集

            BufferedReader reader = Files.newBufferedReader(Paths.get("F:\\Members.sql"), StandardCharsets.UTF_16);

            BufferedWriter writer = Files.newBufferedWriter(Paths.get("F:\\Members_mysql.sql"), StandardCharsets.UTF_8);

            String line = null;

            Matcher matcher = null;

            String matcherStr = null;

            String reg = ".*((?i)CAST\\(0x[0-9-a-f-A-F]+ AS DateTime\\)).*"; // ( 为特殊字符，表示普通的( 需要用 \\( 来转义表示

            Pattern pattern = Pattern.compile(reg);

            while ((line = reader.readLine()) != null) {

                while (line.matches(reg)) {

                    matcher = pattern.matcher(line);

                    matcherStr = null;

                    if (matcher.find()) {

                        matcherStr = matcher.group(1);    // matcherStr = "CAST(0x00009E0E0095524F AS DateTime)"

                        if (matcherStr != null) {

                            String mysqlStr = convertRawDateTimeToStr(matcherStr);

                            if(mysqlStr != null)

                                line = line.replace(matcherStr, " '"+mysqlStr+"' "); // mysqlStr = '2010-10-13 09:03:39'

                        }

                    } else {

                        break;    // break inner while loop

                    }

                }

                writer.write(line);

                writer.newLine();

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

        System.out.println("done.");

    }

}

正则表达式 String reg = ".*((?i)CAST\\(0x[0-9-a-f-A-F]+ AS DateTime\\)).*"; 其中的 (?i) 表示不区分字符的大小写，类似于js中的 /xx/i

在 F:\\Members.sql 中保存的是sql server导出的insert语句形式的数据：

INSERT [Members] ([g_MemberID], [FK_GroupId], [FK_UserId], [FK_C_UserId], [g_Member_State], [g_Member_Jobtitle], [g_Member_Open], [g_Member_Nickname], [g_Member_FirstnamePY], [g_Member_Firstname], [g_Member_LastnamePY], [g_Member_Lastname], [g_Member_sex], [g_Member_Birthday], [g_Member_MaritalStatus], [FK_City], [g_Member_FacePath], [g_Member_Address], [g_Member_Mobile], [g_Member_hometel], [g_Member_Email], [g_Member_QQ], [g_Member_Wangwang], [g_Member_Msn], [g_Member_Note], [g_Member_JoinTime], [FK_User_AttnUserID], [g_push], [g_Member_Order], [gmt_last_modified]) VALUES (1, 1, 4, 4, 40, NULL, 30, N'大手机', N'', N'', N'总', N'总', N'M', N'2013-11-31', NULL, 11, N'20131231/2013123115MF0DD3.jpg', N'', N'', N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, NULL, CAST(0x0000A2A500FC2E4F AS DateTime))

INSERT [Members] ([g_MemberID], [FK_GroupId], [FK_UserId], [FK_C_UserId], [g_Member_State], [g_Member_Jobtitle], [g_Member_Open], [g_Member_Nickname], [g_Member_FirstnamePY], [g_Member_Firstname], [g_Member_LastnamePY], [g_Member_Lastname], [g_Member_sex], [g_Member_Birthday], [g_Member_MaritalStatus], [FK_City], [g_Member_FacePath], [g_Member_Address], [g_Member_Mobile], [g_Member_hometel], [g_Member_Email], [g_Member_QQ], [g_Member_Wangwang], [g_Member_Msn], [g_Member_Note], [g_Member_JoinTime], [FK_User_AttnUserID], [g_push], [g_Member_Order], [gmt_last_modified]) VALUES (2, 2, 4, 4, 40, N'锦鲤', 30, N'', N'', N'测试', N'总', N'陈', N'M', N'2013-11-31', 0, 11, N'20140324/2014032409Xva2Ix.jpg', N'', N'', N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, NULL, CAST(0x0000A2F80095DF52 AS DateTime))

转换之后的结果保存在 F:\\Members_mysql.sql 中，类似于：

INSERT [Members] ([g_MemberID], [FK_GroupId], [FK_UserId], [FK_C_UserId], [g_Member_State], [g_Member_Jobtitle], [g_Member_Open], [g_Member_Nickname], [g_Member_FirstnamePY], [g_Member_Firstname], [g_Member_LastnamePY], [g_Member_Lastname], [g_Member_sex], [g_Member_Birthday], [g_Member_MaritalStatus], [FK_City], [g_Member_FacePath], [g_Member_Address], [g_Member_Mobile], [g_Member_hometel], [g_Member_Email], [g_Member_QQ], [g_Member_Wangwang], [g_Member_Msn], [g_Member_Note], [g_Member_JoinTime], [FK_User_AttnUserID], [g_push], [g_Member_Order], [gmt_last_modified]) VALUES (1, 1, 4, 4, 40, NULL, 30, N'大手机', N'', N'', N'总', N'总', N'M', N'2013-11-31', NULL, 11, N'20131231/2013123115MF0DD3.jpg', N'', N'', N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, NULL,  '2013-12-31 15:18:09' )

INSERT [Members] ([g_MemberID], [FK_GroupId], [FK_UserId], [FK_C_UserId], [g_Member_State], [g_Member_Jobtitle], [g_Member_Open], [g_Member_Nickname], [g_Member_FirstnamePY], [g_Member_Firstname], [g_Member_LastnamePY], [g_Member_Lastname], [g_Member_sex], [g_Member_Birthday], [g_Member_MaritalStatus], [FK_City], [g_Member_FacePath], [g_Member_Address], [g_Member_Mobile], [g_Member_hometel], [g_Member_Email], [g_Member_QQ], [g_Member_Wangwang], [g_Member_Msn], [g_Member_Note], [g_Member_JoinTime], [FK_User_AttnUserID], [g_push], [g_Member_Order], [gmt_last_modified]) VALUES (2, 2, 4, 4, 40, N'锦鲤', 30, N'', N'', N'测试', N'总', N'陈', N'M', N'2013-11-31', 0, 11, N'20140324/2014032409Xva2Ix.jpg', N'', N'', N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, NULL,  '2014-03-24 09:05:40.358' )

可以看到

CAST(0x0000A2A500FC2E4F AS DateTime) 被转换成了：'2013-12-31 15:18:09'

CAST(0x0000A2F80095DF52 AS DateTime) 被转换成了：'2014-03-24 09:05:40'

当然要达到直接运行插入mysql数据库中，还要去掉 [g_MemberID] 中的 [] 中括号，还有每一行的末尾要加上逗号 , 再者 INSERT [Members] ([g_MemberID], [FK_GroupId], ...) 语句只需要出现一次，采用 insert into tab(col_a,col_b,col_c) values (1,2,3), (4,5,6) ... 这样的插入形式，可以明显提高插入速度。

另外，如果sql server导出时，如果选择导出到excel时，datetime字段的值是不需要进行转换的。在数据量很小时，可以选择这种方式。

2. 处理 sql server 导出的 decimal 类型字段 和 insert 语句

sql server在导出 decimal 类型的字段时，导出的结果是类似于：CAST(5.00 AS Decimal(6, 2)) 这样的形式，我们需要将其转换成 5.00，还有上面说到的要去掉[g_MemberID] 中的 [] 中括号, 每一行的末尾要加上逗号，还有要转换成 insert into tab(col_a,col_b,col_c) values (1,2,3), (4,5,6), ....; 形式的结果。下面给出一个相对完整的处理代码：

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.nio.charset.Charset;

import java.nio.charset.StandardCharsets;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import java.util.regex.Matcher;

import java.util.regex.Pattern;

/**

 * sql server数据库在向mysql等数据库迁移数据时，会遇到几个麻烦

 * 1) datetime 类型字段的导出结果需要转换；

 * 2) decimal 类型的字段的导出结果需要转换；

 * 3) insert 语句需要去掉 [和 ], 去掉多余的 insert, 最后变成  insert into user(id,name) values(1,'aa'), (2,'bb'); 的形式

 * @author digdeep@126.com

 */

public class SqlServerConverter {

    private static final int insertSQLcount = 10;

    private static String reg = ".*((?i)CAST\\(0x[0-9-a-f-A-F]+ AS DateTime\\)).*"; // CAST(0x00009E0E0095524F AS DateTime)

    private static Pattern pattern = Pattern.compile(reg);

    private static String sqlInertReg = ".*((?i)INSERT .* \\(.*\\) VALUES )\\(.*\\).*"; //insert member (id,name) values (xxx)

    private static Pattern sqlInertPattern = Pattern.compile(sqlInertReg);

    private static String decimalReg = ".*((?i)CAST\\(.* AS Decimal\\(.*\\)\\)).*";;    //CAST(5.00 AS Decimal(6, 2))

    private static Pattern decimalPattern = Pattern.compile(decimalReg); 

    /**

     * 将每一条insert语句中的 "CAST(0x00009E0E0095524F AS DateTime)" 转换成对应的：'2010-10-13 09:03:39'

     * @param line

     * @return

     */

    public static String dealWithDateTime(String line){

        Matcher matcher = null;

        String matcherStr = null;

        while (line.matches(reg)) {

            matcher = pattern.matcher(line);

            matcherStr = null;

            if (matcher.find()) {

                matcherStr = matcher.group(1);    // matcherStr = "CAST(0x00009E0E0095524F AS DateTime)"

                if (matcherStr != null) {

                    String mysqlStr = SqlServerDateTimeUtils.convertRawDateTimeToStr(matcherStr);

                    if(mysqlStr != null)

                        line = line.replace(matcherStr, " '"+mysqlStr+"' "); // mysqlStr = '2010-10-13 09:03:39'

                }

            } else {

                break;    // break inner while loop

            }

        }

        return line;

    }

    /**

     * 将每一条insert语句中的 "CAST(5.00 AS Decimal(6, 2))" 转换成对应的：5.00

     * @param line

     * @return

     */

    public static String dealWithDecimal(String line){

        Matcher matcher = null;

        String matcherStr = null;

        while (line.matches(decimalReg)) {

            matcher = decimalPattern.matcher(line);

            matcherStr = null;

            if (matcher.find()) {

                matcherStr = matcher.group(1);    // matcherStr = CAST(5.00 AS Decimal(6, 2))

                if (matcherStr != null) {

                    String result = matcherStr.substring("CAST(".length(),

                                                matcherStr.toUpperCase().indexOf(" AS DECIMAL("));

                    line = line.replace(matcherStr, result);

                }

            } else {

                break;    // break inner while loop

            }

        }

        return line;

    }

    /**

     * @param sqlServerPath 输入文件

     * @param sqlCharset 输入文件的编码

     * @param mysqlPath 输出文件，以utf-8格式输出

     * @return

     */

    public static boolean converterSqlServerToMySQL(String sqlServerFile, Charset sqlServerCharset, String mysqlFile){

        Path inPath = Paths.get(sqlServerFile);

        Path outPath = Paths.get(mysqlFile);

        // writer 采用 StandardCharsets.UTF_8

        try(BufferedReader reader = Files.newBufferedReader(inPath, sqlServerCharset);

             BufferedWriter writer = Files.newBufferedWriter(outPath, StandardCharsets.UTF_8);){

            String line = null;

            Matcher matcher = null;

            String matcherStr = null;

            int i = 0;

            while ((line = reader.readLine()) != null) {

                // 1. 转换 datetime: CAST(0x00009E0E0095524F AS DateTime) 转换成 '2010-10-13 09:03:39'

                line = dealWithDateTime(line);

                // 2. 转换decimal: CAST(5.00 AS Decimal(6, 2)) 转换成 5.00

                line = dealWithDecimal(line);

                // 3. 去掉 [ 和 ], 去掉多余的 insert, 最后变成  insert into user(id,name) values(1,'aa'), (2,'bb'); 的形式

                matcher = sqlInertPattern.matcher(line);

                if(matcher.find()){

                    i++;

                    matcherStr = matcher.group(1);    // matcherStr ==  insert [user]([id], [name]) values (1,'aa')

                    if(i % insertSQLcount == 1){    // 每 insertSQLcount 条 insert 作为一组进行提交

                        if(i > 1){

                            writer.write(";");    // 加入 ; 进行提交

                            writer.newLine();

                        }

                        line = line.replace(matcherStr, " ");    // line ==  (1,'aa')

                        matcherStr = matcherStr.replace("[", "");

                        matcherStr = matcherStr.replace("]", "");

                        matcherStr = matcherStr.toLowerCase().replace("insert", "insert into ");

                        line = matcherStr + "\n" + line;    // line == insert into user(id,name) \n values(1,'aa')

                    }else{

                        line = line.replace(matcherStr, ",");    // line == ,values(2,'aa')

                    }

                    writer.write(line);

                    writer.newLine();

                }

            }

            if(i % insertSQLcount != 1 && i > 0) // 当末尾没有封号(;)， 时才添加一个封号(;)，避免在末尾出现两个封号(;;)

                writer.write(";");

            writer.newLine();

            writer.flush();        // 注意这里一定要 flush ，不然文档的最后面的最后一两条数据不会完整写到文件中的！！！

        } catch (Exception e) {

            e.printStackTrace();

            return false;

        }

        System.out.println("OK.");

        return true;

    }

    public static void main(String[] args) {

        // 这里的字符集一般是 StandardCharsets.UTF_16 或者 StandardCharsets.UTF_8，具体看你导出时采用的是那种字符集

        // 如果sql server导出时选择了 unicode，那么这里就应该使用 StandardCharsets.UTF_16

        SqlServerConverter.converterSqlServerToMySQL("F:\\Members.sql", StandardCharsets.UTF_16,

                                                    "F:\\Members_mysql.sql");

        System.out.println("-------------------------------------------------");

        SqlServerConverter.converterSqlServerToMySQL("F:\\model_choise.sql", StandardCharsets.UTF_16,

                                                        "F:\\model_choise_mysql.sql");

        System.out.println("done.");

    }

}

转换之前 F:\\model_choise.sql 的文件内容如下所示：

SET IDENTITY_INSERT [model_choise] ON

INSERT [model_choise] ([id], [gid], [member_id], [user_id], [model_id], [status], [choise_time], [item_id], [pay_amount], [accuracy], [g_name], [g_mobile], [reation_id]) VALUES (992, NULL, NULL, 736, 481, 1, CAST(0x0000A49500C0DE5F AS DateTime), NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [model_choise] ([id], [gid], [member_id], [user_id], [model_id], [status], [choise_time], [item_id], [pay_amount], [accuracy], [g_name], [g_mobile], [reation_id]) VALUES (993, NULL, NULL, 737, 559, 1, CAST(0x0000A49501420847 AS DateTime), NULL, NULL, NULL, NULL, NULL, NULL)

INSERT [model_choise] ([id], [gid], [member_id], [user_id], [model_id], [status], [choise_time], [item_id], [pay_amount], [accuracy], [g_name], [g_mobile], [reation_id]) VALUES (1009, NULL, NULL, 9, 528, 1, CAST(0x0000A496013F1119 AS DateTime), NULL, NULL, CAST(5.00 AS Decimal(6, 2)), NULL, NULL, NULL)

INSERT [model_choise] ([id], [gid], [member_id], [user_id], [model_id], [status], [choise_time], [item_id], [pay_amount], [accuracy], [g_name], [g_mobile], [reation_id]) VALUES (1010, NULL, NULL, 4, 528, 1, CAST(0x0000A496013F9F76 AS DateTime), NULL, NULL, CAST(5.00 AS Decimal(6, 2)), NULL, NULL, NULL)

// ... ...

转换之后 F:\\model_choise_mysql.sql 内容如下所示：

insert into  model_choise (id, gid, member_id, user_id, model_id, status, choise_time, item_id, pay_amount, accuracy, g_name, g_mobile, reation_id) values

 (992, NULL, NULL, 736, 481, 1,  '2015-05-11 11:42:12' , NULL, NULL, NULL, NULL, NULL, NULL)

,(993, NULL, NULL, 737, 559, 1,  '2015-05-11 19:32:28' , NULL, NULL, NULL, NULL, NULL, NULL)

,(1009, NULL, NULL, 9, 528, 1,  '2015-05-12 19:21:41' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1010, NULL, NULL, 4, 528, 1,  '2015-05-12 19:23:42' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1011, NULL, NULL, 4, 495, 1,  '2015-05-12 19:24:38' , NULL, NULL, NULL, NULL, NULL, NULL)

,(1012, NULL, NULL, 4, 554, 1,  '2015-05-12 19:25:23' , NULL, NULL, NULL, NULL, NULL, NULL)

,(1013, NULL, NULL, 566, 528, -1,  '2015-05-12 20:33:24' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1014, NULL, NULL, 8, 495, -1,  '2015-05-12 21:03:45' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1015, NULL, NULL, 8, 498, -1,  '2015-05-12 21:04:15' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1016, NULL, NULL, 8, 558, -1,  '2015-05-12 21:04:39' , NULL, NULL, 5.00, NULL, NULL, NULL)

;

insert into  model_choise (id, gid, member_id, user_id, model_id, status, choise_time, item_id, pay_amount, accuracy, g_name, g_mobile, reation_id) values

 (1017, NULL, NULL, 8, 417, -1,  '2015-05-12 21:05:29' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1018, NULL, NULL, 8, 494, -1,  '2015-05-12 21:29:20' , NULL, NULL, 4.00, NULL, NULL, NULL)

,(1019, NULL, NULL, 8, 528, -1,  '2015-05-12 21:31:41' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1026, NULL, NULL, 566, 493, 1,  '2015-05-13 02:14:09' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1027, NULL, NULL, 566, 532, 1,  '2015-05-13 02:14:17' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1028, NULL, NULL, 566, 459, 1,  '2015-05-13 09:16:44' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1029, NULL, NULL, 566, 421, -1,  '2015-05-13 10:03:52' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1032, NULL, NULL, 9, 495, 1,  '2015-05-13 13:27:32' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1033, NULL, NULL, 9, 554, 1,  '2015-05-13 13:28:21' , NULL, NULL, 5.00, NULL, NULL, NULL)

,(1034, NULL, NULL, 9, 560, -1,  '2015-05-13 17:10:21' , NULL, NULL, NULL, NULL, NULL, NULL)

;
// ... ...

可以看到  CAST(0x0000A496013F1119 AS DateTime), 和 CAST(5.00 AS Decimal(6, 2)) 都被正确的转换了。insert 语句的转换也是完全正确的。所以转换之后的文件 F:\\model_choise_mysql.sql 是可以如下使用 source 命令来直接插入mysql数据库中:

mysql> source F:\\model_choise_mysql.sql

Query OK, 10 rows affected (0.03 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.05 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.04 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.04 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.05 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.02 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.02 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 10 rows affected (0.02 sec)

Records: 10  Duplicates: 0  Warnings: 0

Query OK, 8 rows affected (0.03 sec)

Records: 8  Duplicates: 0  Warnings: 0

mysql> select count(*) from model_choise;

+----------+

| count(*) |

+----------+

|       88 |

+----------+

1 row in set (0.00 sec)

实际使用时，private static final int insertSQLcount = 10;  insertSQLcount  应该调大一点，几百，上千都是可以的。

另外上面使用了 Java 1.7 中新引入的 try-with-recourse 的自动关闭资源的使用方法:

try(BufferedReader reader = Files.newBufferedReader(inPath, StandardCharsets.UTF_16);

             BufferedWriter writer = Files.newBufferedWriter(outPath, StandardCharsets.UTF_8);)

相关文章