Geek Answers Handbook

When importing sql table receiving error due to sorting

The code to create the table is below when I try to import this table into a new database, 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 the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax use about '(6) NOT NULL, created_by int (20) NOT NULL,
updated_on datetime (6) NOT NU 'on line 11

kindly help me solve these problems, yet i need to import many tables

+5
source share
6 answers

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 ;
+4
source

You cannot specify the length attribute for a datetime column:

 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 ;
+4
source

Do not set the length in datetime . Just use it like

 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,// remove length here `created_by` int(20) NOT NULL, `updated_on` datetime NOT NULL,// remove length here `updated_by` int(20) NOT NULL, PRIMARY KEY (`blog_id`) )Engine=InnoDB AUTO_INCREMENT=14
+4
source

I do not know why you use length 6 in your Datetime fields. Remove the length and everything will be fine.

+2
source

When using MySQL 5.5, this leads to an error when adding length to the datetime, so for this you have to write

 REATE 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 ;

This, however, will work when using MySQL 5.6.
It will not add length to the datetime , but MySQL 5.6 will ignore the length and execute it as a normal datetime field.

0
source

Thanks everyone for your suggestions, now it works for me

 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 ;

when we use datetime in the table, it automatically allocates memory size, so we do not need to specify it

0
source

More articles:


All Articles