linux5.6以下版本的不相容問題

之前一直用的都是mysql5.6版本，最近突然使用到了mysql5.1版本，於是在匯入資料的時候便出現了很多由於版本不相容的問題。

 

1.mysql5.1沒有datetime型別，所以對於時間型別，只能使用timestamp

例：

`FRecordTime` datetime DEFAULT CURRENT_TIMESTAMP

需要改為

`FRecordTime` timestamp DEFAULT CURRENT_TIMESTAMP,

 

2.mysql5.6版本以下不允許一個表有兩個current_timestamp

例：

當你建立該表時：

CREATE TABLE `example` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastUpdated` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

會出錯，錯誤資訊為：

ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

意思是隻能有一個帶CURRENT_TIMESTAMP的timestamp列存在。其實在mysql5.5中存在這麼一句話：

 One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column,
as the auto-update value, or both. It is not possible to have the current timestamp be the 
 default value for one column and the auto-update value for another column.

而在mysql5.6中則進行了修改：

 Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time.
 This restriction has been lifted. Any TIMESTAMP column definition can have any combination 
 of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses.
In addition, these clauses now can be used with DATETIME column definitions. 
 For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME.

即允許了這種情況出現。

解決方法：

可以用觸發器實現上述表的建立：

CREATE TABLE `example` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastUpdated` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
DROP TRIGGER IF EXISTS `update_example_trigger`;
DELIMITER //
CREATE TRIGGER `update_example_trigger` BEFORE UPDATE ON `example`
 FOR EACH ROW SET NEW.`lastUpdated` = NOW()
//
DELIMITER ;

 

3.mysql5.1的limit語句後面只允許跟常量不允許是變數

在儲存過程的中，mysql5.6允許limit後面直接跟著輸入引數，但是mysql5.1只允許limit後面跟著常量。

解決方法：

1）採用動態的方式

PREPARE stmt1 FROM 'select * from users LIMIT ?,?';
SET @a = ino;
SET @b = pagecount
EXECUTE stmt1 USING @a, @b;

2）將變數事先轉換為字元：

set @dd=conact('select * from users LIMIT',ino,pagecount)
PREPARE stmt1 FROM dd
EXECUTE stmt1