因项目需要，要将MySQL导入SQL server数据库。折腾了好几天。记录一下！
论mysql与sqlserver不同：
1：创建语句不一样：
Mysql：
CREATE TABLE `department` (
`id` int unsigned NOT NULL COMMENT '主键',
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
`code` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门编码',
`parent_id` int(0) DEFAULT NULL COMMENT '上级部门ID',
`sequence` int(0) DEFAULT NULL COMMENT '排序号',
`type_id` int(0) DEFAULT NULL COMMENT '部门类型ID',
`description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 94 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SQL server：
CREATE TABLE department (
id int NOT NULL PRIMARY KEY,
name varchar(50) COLLATE Chinese_PRC_CI_AS NULL ,
code varchar(50) COLLATE Chinese_PRC_CI_AS NULL ,
parent_id int DEFAULT NULL ,
sequence int DEFAULT NULL ,
type_id int DEFAULT NULL ,
description varchar(255) COLLATE Chinese_PRC_CI_AS NULL
)
注意：
1：mysql中的表名，字段名的引号，SQL server不识别，SQL server可以用[] 替换。
2：mysql的utf8编码，sqlserver不识别；
3：mysql的备注，直接用COMMENT '部门编码'，SQL server不识别。
4：mysql的主外键，SQL server不识别。
目前相关mysql直接转SQL server的工具，没有直接转换成功的。
2、查询语句：
MySQL：
SELECT * FROM department ORDER BY id ASC LIMIT 1 OFFSET 2
SQL server：
SELECT TOP 3 WITH TIES * FROM department ORDER BY id ASC
3、Java操作，批量插入语句：
<!-- 批量插入记录 -->
<insert id="batchInsert" parameterType="java.util.List">
insert into wms_location_stereoscopic (
location_id
,warehouse_code
,area_code
,location_code
,location_attr
,location_desc
,pallet_code
,in_seq
,out_seq
,floor_number
,shelves_number
,layer_number
,column_number
,use_status
,allow_mix
,user_defined1
,user_defined2
,user_defined3
,user_defined4
,user_defined5
,create_by
,gmt_create
,last_modified_by
,gmt_modified
,active_flag
)
values
<foreach collection="list" item="item" index="index" separator="," >
(
#{item.locationId}
,#{item.warehouseCode}
,#{item.areaCode}
,#{item.locationCode}
,#{item.locationAttr}
,#{item.locationDesc}
,#{item.palletCode}
,#{item.inSeq}
,#{item.outSeq}
,#{item.floorNumber}
,#{item.shelvesNumber}
,#{item.layerNumber}
,#{item.columnNumber}
,#{item.useStatus}
,#{item.allowMix}
,#{item.userDefined1}
,#{item.userDefined2}
,#{item.userDefined3}
,#{item.userDefined4}
,#{item.userDefined5}
,#{item.createBy}
,#{item.gmtCreate}
,#{item.lastModifiedBy}
,#{item.gmtModified}
,#{item.activeFlag}
)
</foreach>
</insert>
MySQL：可大量插入
SQL server：插入有限制，参数不能超过2100
SQL server需要代码处理，每次插入的条数，根据参数来确定。
public Resp batchInsertLocation(List<WmsLocationStereoscopic> insertList) {
//批量插入，因为sqlserver参数限制2100个。
if(insertList!=null &&insertList.size()>0){
if(insertList.size()<80) {
this.wmsLocationStereoscopicMapper.batchInsert(insertList);
}else {
// 每次插入数据库的数据量
int preInsertDataCount = 80;
// 可遍历的插入数据库的次数
int insertSqlCount = 0;
// 总数据条数
int totalDataCount=insertList.size();
if(totalDataCount % preInsertDataCount==0){
insertSqlCount=totalDataCount/preInsertDataCount;
}else
{
insertSqlCount=totalDataCount/preInsertDataCount+1;
}
for (int i = 0; i < insertSqlCount; i++) {
int startNumber = i*preInsertDataCount;
int endUnmber=(i+1)*preInsertDataCount;
if(endUnmber>totalDataCount){
endUnmber=totalDataCount;
}
List<WmsLocationStereoscopic> subListOK = insertList.subList(startNumber,endUnmber);
this.wmsLocationStereoscopicMapper.batchInsert(subListOK);
}
}
}
return this.success();
}
4、Mybatis操作聚合函数
SELECT
count( 1 )
FROM
(
SELECT
wms_order_check_.check_id,
CASE
WHEN sum( s.diff_flag ) > 0 THEN
1 ELSE 0
END diff_flag,
wms_order_check_.order_no,
wms_order_check_.order_type,
wms_order_check_.order_status,
wms_order_check_.area_code,
wms_order_check_.goods_code,
wms_order_check_.goods_name,
wms_order_check_.location_code,
wms_order_check_.gmt_create,
wms_order_check_.user_defined2,
sum( CASE WHEN s.amount IS NULL THEN 0 WHEN s.amount = '' THEN 0 ELSE 1 END ) planPalletAmount,
sum( s.user_defined1 ) realPalletAmount,
sum( s.amount ) planGoodsAmount,
sum( s.real_amount ) realGoodsAmount
FROM
wms_order_check wms_order_check_
LEFT JOIN wms_order_check_pallet s ON wms_order_check_.check_id = s.check_id
WHERE
wms_order_check_.order_type = '2'
GROUP BY
wms_order_check_.order_no,
wms_order_check_.check_id,
wms_order_check_.goods_code UNION
SELECT
wms_order_check_.check_id,
CASE
WHEN sum( s.diff_flag ) > 0 THEN
1 ELSE 0
END diff_flag,
wms_order_check_.order_no,
wms_order_check_.order_type,
wms_order_check_.order_status,
wms_order_check_.area_code,
wms_order_check_.goods_code,
wms_order_check_.goods_name,
wms_order_check_.location_code,
wms_order_check_.gmt_create,
wms_order_check_.user_defined2,
sum( CASE WHEN s.amount IS NULL THEN 0 WHEN s.amount = '' THEN 0 ELSE 1 END ) planPalletAmount,
sum( s.user_defined1 ) realPalletAmount,
sum( s.amount ) planGoodsAmount,
sum( s.real_amount ) realGoodsAmount
FROM
wms_order_check wms_order_check_
LEFT JOIN wms_order_check_pallet s ON wms_order_check_.check_id = s.check_id
WHERE
wms_order_check_.order_type = '1'
GROUP BY
wms_order_check_.order_no,
wms_order_check_.location_code
) a
Mysql没问题，SQL server提示：操作数数据类型 char/verchar 对于 sum 运算符无效。
5、其他
莫名奇妙的一些不识别，一点点调试吧！
