【Mysql】[Err] 1293 - Incorrect table definition；

基本情况

SQL文件描述

/*
Navicat MySQL Data Transfer

Source Server         : cm4生产-200
Source Server Version : 50725
Source Host           : 192.168.1.200:3306
Source Database       : db_wms

Target Server Type    : MYSQL
Target Server Version : 50725
File Encoding         : 65001

Date: 2023-03-14 16:59:12
*/

导入的数据库版本

SELECT VERSION()

---

5.5.50

---

导入参数

[Err] 1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

sql文件异常语句位置

-- ----------------------------
-- Table structure for `tb_groups_message`
-- ----------------------------
DROP TABLE IF EXISTS `tb_groups_message`;
CREATE TABLE `tb_groups_message` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `GROUP_ID` int(255) DEFAULT NULL,
  `TAG_MAC` int(11) DEFAULT NULL,
  `BIN_ID` int(11) DEFAULT NULL,
  `WIP_NO` varchar(255) DEFAULT NULL,
  `PART_NO` varchar(255) DEFAULT NULL,
  `ITEM_COUNT` int(11) DEFAULT NULL,
  `LED_STATE` int(11) DEFAULT NULL,
  `LED_COLOR` varchar(11) DEFAULT NULL,
  `UPDATE_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `tb_orders`
-- ----------------------------
DROP TABLE IF EXISTS `tb_orders`;
CREATE TABLE `tb_orders` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `orderNumber` varchar(25) COLLATE utf8mb4_bin NOT NULL COMMENT '订单编号',
  `status` tinyint(1) NOT NULL COMMENT '状态',
  `establish` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `finish` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '完成时间',
  `shopNumber` varchar(8) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

问题分析

• MySql 5.5 每个表只能有一个Timestamp 设置根据当前时间戳更新属性。
• MySql 5.6及以上 每个表允许多个Timestamp 字段 同时设置根据当前时间戳更新属性。
  

解决方案

一 、手动修改后重新导入

只保留一个在更新时设置默认值CURRENT_TIMESTAMP

-- ----------------------------
-- Table structure for `tb_groups_message`
-- ----------------------------
DROP TABLE IF EXISTS `tb_groups_message`;
CREATE TABLE `tb_groups_message` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `GROUP_ID` int(255) DEFAULT NULL,
  `TAG_MAC` int(11) DEFAULT NULL,
  `BIN_ID` int(11) DEFAULT NULL,
  `WIP_NO` varchar(255) DEFAULT NULL,
  `PART_NO` varchar(255) DEFAULT NULL,
  `ITEM_COUNT` int(11) DEFAULT NULL,
  `LED_STATE` int(11) DEFAULT NULL,
  `LED_COLOR` varchar(11) DEFAULT NULL,
  `UPDATE_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time` timestamp NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `tb_orders`
-- ----------------------------
DROP TABLE IF EXISTS `tb_orders`;
CREATE TABLE `tb_orders` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `orderNumber` varchar(25) COLLATE utf8mb4_bin NOT NULL COMMENT '订单编号',
  `status` tinyint(1) NOT NULL COMMENT '状态',
  `establish` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `finish` timestamp NULL COMMENT '完成时间',
  `shopNumber` varchar(8) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

二、重装相应版本的数据库

安装mysql-5.7.40-winx64.zip