你的位置:首页 > 软件开发 > 数据库 > sql server 导出的datetime结果 CAST(0x00009E0E0095524F AS DateTime) 如何向mysql,oracle等数据库进行转换

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

发布时间:2015-09-20 00:00:45
在进行sql server向mysql等其他数据进行迁移数据时,会发现使用sql server导出的datetime类型的结果是16进制表示的二进制的结果,类似于:CAST(0x00009E0E0095524F AS DateTime),这样形式的datetime是无法向其他数据 ...

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

注意,因为datetime分为了4字节形式和8字节形式,所有对应的又两种转换方法。另外本转义方法,只能精确到秒级别,毫秒级别是不精确的。如果需要完全精确的转换,请采用其他工具进行转换,比如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://stackoverflow.com/questions/12033598/cast-hex-as-datatime-how-to-get-date * @author digdeep@126.com */public class SqlServerDateTimeToMysql {  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);  }  /**   * 将sql server导出的datetime字段结果 CAST(0x00009E0E0095524F AS DateTime),转换成   * 2010-10-13 09:03:39 ,以利于 sql server 向 mysql, oracle 迁移数据   * @param dateTimeStr   * @return   * reference: http://stackoverflow.com/questions/12033598/cast-hex-as-datatime-how-to-get-date   */  public static String convertSqlServerDateTimeToMySQLDateTime(String dateTimeStr){    return convertToMySQLDateTime(dateTimeStr, false);  }    /**   * 将sql server导出的datetime字段结果 CAST(0x00009E0E0095524F AS DateTime),转换成   * 2010-10-13 09:03:39 或者 2010-10-13 09:03:39.394 注意毫秒部分不精确   * @param dateTimeStr   * @param millisecondFlag   * @return   * reference: http://stackoverflow.com/questions/12033598/cast-hex-as-datatime-how-to-get-date   */  public static String convertToMySQLDateTime(String dateTimeStr, boolean millisecondFlag) {    if (dateTimeStr == null || dateTimeStr.trim().equals(""))      return null;    String rawData = dateTimeStr.substring("CAST(".length(), dateTimeStr.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 = getMySQLDateTime4Bytes(rawData);    }    if(rawData.length() > 10 && rawData.length() <= 18){  // rowData = "0x00009E0E0095524F"      if(millisecondFlag)        result = getMySQLDateTime8Bytes2(rawData);  // 返回值带毫秒,毫秒部分不精确      else        result = getMySQLDateTime8Bytes(rawData);  // 精确到秒,返回值不带毫秒    }    return result;  }    /**   * sql server 利用 Small DateTime 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 getMySQLDateTime4Bytes(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   * @return mysqlDateTime   *   */  private static String getMySQLDateTime8Bytes(String rawData){    String day = rawData.substring(2, 2 + 8);    String seconds = rawData.substring(10, 10 + 8);        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, (int)(Integer.parseInt(seconds, 16)*3.33333/1000)); // 3.33333 后面的3越多越精确,带4位小数就行了    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");          return sdf.format(calendar.getTime());  }    /**   * sql server DateTime 利用 8 字节表示 , 该转换精确到秒,有毫秒部分   * @param rawData   * @return mysqlDateTime   */  private static String getMySQLDateTime8Bytes2(String rawData){    String day = rawData.substring(2, 2 + 8);    String seconds = rawData.substring(10, 10 + 8);        Calendar calendar = Calendar.getInstance();    calendar.setTimeInMillis(cal.getTimeInMillis());  // 1900-01-01 00:00:00    calendar.add(Calendar.DATE, Integer.parseInt(day, 16));//    BigDecimal decimal = new BigDecimal("3.3333333333");  // 使用BigDecimal在精度方面似乎没有什么作用//    decimal = decimal.multiply(new BigDecimal(Integer.parseInt(seconds, 16)));//    decimal = decimal.divide(new BigDecimal("1000"));//    calendar.add(Calendar.MILLISECOND, (int)(Integer.parseInt(seconds, 16)*3.33333));//    calendar.add(Calendar.MILLISECOND, decimal.intValue());//    calendar.add(Calendar.SECOND, decimal.intValue());  // 使用 Calendar.MILLISECOND 来处理,反而会导致秒级别的误差!不知道为什么        calendar.add(Calendar.SECOND, (int)(Integer.parseInt(seconds, 16)*3.33333/1000));//   calendar.add(Calendar.MILLISECOND, (int)(Integer.parseInt(seconds, 16)*3.33333)); // Calendar.MILLISECOND 会导致秒级别的误差    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");  // 毫秒部分是不精确的,每次运行的结果不相同!          return sdf.format(calendar.getTime());  }    public static void main(String[] args) {        // select CAST(0x00009E0E0095524F AS DateTime) == 2010-10-13 09:03:39.783 正确的日期值    String str = "CAST(0x00009E0E0095524F AS DateTime)";      System.out.println(convertToMySQLDateTime(str, true));  // 2010-10-13 09:03:39.374 毫秒部分不精确    System.out.println(convertSqlServerDateTimeToMySQLDateTime(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_16);            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 = convertSqlServerDateTimeToMySQLDateTime(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.");  }  }

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

关键词:MYSQL

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。