MySql版本为sql5
一、执行sql文件:
步骤一:创建数据库;
步骤二: 设置名称和字符集;
步骤三:选择执行脚本;
步骤四:选择执行脚本,且设置额外的命令参数;
步骤五:执行完毕。
二、产生错误类型:
1、在执行过程中报错:ERROR 1115 (42000) at line 17: Unknown character set: 'utf8mb4'
该错误源于版本不对应,'utf8mb4'是sql5.6版本以上对应的
解决方法:
一、将版本升级,但在升级过程中注意要卸载干净;
二、将utf8mb4修改为utf8即可;
2、报错:ERROR 1064 (42000) at line 3842: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(0) NOT NULL COMMENT '閸掓稑缂撻弮銉︽埂',
`product_category_id` int(10) NOT NULL CO' at line 7
代码如下:
CREATE TABLE `product` (
`product_id` int(10) NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
`product_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品标题',
`product_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '原价',
`product_sale_price` decimal(10, 2) NOT NULL COMMENT '促销价',
`product_create_date` datetime(0) NOT NULL COMMENT '创建日期',
`product_category_id` int(10) NOT NULL COMMENT '类别id',
`product_isEnabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否可用',
PRIMARY KEY (`product_id`) USING BTREE,
INDEX `product_ibfk_1`(`product_category_id`) USING BTREE,
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`product_category_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE ON UPDATE RESTRICT
)
改进后代码:
CREATE TABLE `product` (
`product_id` int(10) NOT NULL AUTO_INCREMENT,
`product_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
`product_title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '产品标题',
`product_price` decimal(10, 2) NULL DEFAULT NULL COMMENT '原价',
`product_sale_price` decimal(10, 2) NOT NULL COMMENT '促销价',
`product_create_date` datetime NOT NULL COMMENT '创建日期',
`product_category_id` int(10) NOT NULL COMMENT '类别id',
`product_isEnabled` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否可用',
PRIMARY KEY (`product_id`) USING BTREE,
INDEX `product_ibfk_1`(`product_category_id`) USING BTREE,
CONSTRAINT `product_ibfk_1` FOREIGN KEY (`product_category_id`) REFERENCES `category` (`category_id`) ON DELETE CASCADE ON UPDATE RESTRICT
)
原因:
主要是因为mysql5.7和mysql5.5的datetime、timestamp不兼容,mysql5.7导出的格式为 : datetime(0)而mysql5.5则无法识别该语法
解决方法:1、将导出语句中datetime(0) 改为datetime,或者是timestamp(0) 改为timestamp
2、保持数据库版本一致