While importing sql table getting error due to collation

Question

the code for creating table is given below while i try to import this table in new database am getting the following error

Query

CREATE TABLE IF NOT EXISTS `blogs` (
  `blog_id` int(10) NOT NULL AUTO_INCREMENT,
  `blog_title` text NOT NULL,
  `blog_content` text NOT NULL,
  `created_on` datetime(6) NOT NULL,
  `created_by` int(20) NOT NULL,
  `updated_on` datetime(6) NOT NULL,
  `updated_by` int(20) NOT NULL,
  PRIMARY KEY (`blog_id`)
) Engine=InnoDB  AUTO_INCREMENT=14 ;

error:#1064 - 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 '(6) NOT NULL, created_by int(20) NOT NULL,
updated_on datetime(6) NOT NU' at line 11

kindly guide me to solve this issues still i need to import many tables

Answer 1

Remove length (6) from datetime(6).

So your query would look something like this,

CREATE TABLE IF NOT EXISTS `blogs` (
  `blog_id` int(10) NOT NULL AUTO_INCREMENT,
  `blog_title` text NOT NULL,
  `blog_content` text NOT NULL,
  `created_on` datetime NOT NULL,
  `created_by` int(20) NOT NULL,
  `updated_on` datetime NOT NULL,
  `updated_by` int(20) NOT NULL,
  PRIMARY KEY (`blog_id`)
) Engine=InnoDB  AUTO_INCREMENT=14 ;