Search code examples
mysqlsqlstringerror-handlingcreate-table

#1064 - You have an error in your SQL syntax: MAX NULL problem

I'm new to MySQL and ran into a little trouble with a learning project I'm working on.

Whenever I try to create a table
SQL query:

CREATE TABLE IF NOT EXISTS `tweeter` ( 
`id` INT NULL, 
`conversation_id` INT NULL, 
`created_at` INT NULL, 
`date` VARCHAR(MAX) NULL, 
`time` VARCHAR(MAX) NULL, 
`timezone` VARCHAR(MAX) NULL, 
`user_id` INT NULL, 
`username` VARCHAR(MAX) NULL, 
`name` VARCHAR(MAX) NULL, 
`place` VARCHAR(MAX) NULL, 
`tweet` VARCHAR(MAX) NULL, 
`mentions` JSON NULL, 
`urls` JSON NULL, 
`photos` JSON NULL, 
`replies_count` INT NULL, 
`retweets_count` INT NULL, 
`likes_count` INT NULL, 
`hashtags` JSON NULL, 
`cashtags` JSON NULL,   
`link` VARCHAR(MAX) NULL, 
`retweet` BOOL NULL, 
`quote_url` VARCHAR(MAX) NULL, 
`video` INT NULL, 
`near` VARCHAR(MAX) NULL, 
`geo` VARCHAR(MAX) NULL, 
`source` VARCHAR(MAX) NULL, 
`user_rt_id` VARCHAR(MAX) NULL, 
`user_rt` VARCHAR(MAX) NULL, 
`retweet_id` VARCHAR(MAX) NULL, 
`reply_to` JSON NULL, 
`retweet_date` VARCHAR(MAX) NULL, 
`translate` VARCHAR(MAX) NULL, 
`trans_src` VARCHAR(MAX) NULL, 
`trans_dest` VARCHAR(MAX) NULL )

I get the following error message:

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 'MAX) NULL,

`time` VARCHAR(MAX) NULL,
`timezone` VARCHAR(MAX) NULL,
`user_id` INT' at line 5

Here is some info on what I'm working with

  1. WAMPServer
  2. Server type: MySQL
  3. Server version: 8.0.18
  4. phpMyAdmin: 4.9.2.

I've spent a day trying to solve this and now I think its time to ask for help.I was wondering if anyone can tell me what I'm doing wrong?

Solution

  • There is no such thing as VARCHAR(MAX) in MySQL. You do need to specify an actual value. The actual limit is not set on the column itself but on the row, that should not be more than 64 kB.

    You have a lot of VARCHAR columns, so this limit will matter here. Do not blindly set a high value on all your columns; instead, you should tune each column size according to the actually value it will store.

    Also, consider using the proper datatypes rather than strings everywhere. Some of your string columns seem to be dates. For those, use the relevant date-like datatype (DATE, DATETIME, TIMESTAMP or TIME).