Unknown syntax error in MySQL statement

Question

I am using below CREATE TABLE statement

CREATE TABLE IF NOT EXISTS users (
`uuid` varchar(36) NOT NULL,
`json` varchar(MAX) NOT NULL,
PRIMARY KEY (`uuid`)
);

However I keep getting this error.

ERROR 1064 (42000): 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) NOT NULL, PRIMARY KEY (uuid) )' at line 3

Makes no sense to me.

max is a reserved word. how about theMax...no how about a number — Drew, Jun 07 '15 at 00:10
that worked, however is converting the row `json` from varchar to text. — ThatGuy343, Jun 07 '15 at 00:13
sorry i was messing with 60000 once on a dif server to check max but that high i would Text for purposes mainly FULLTEXT search. typically it is like 100 or 255 (<1001) — Drew, Jun 07 '15 at 00:56

score 3 · Accepted Answer · edited May 23 '17 at 11:54

3

MAX is not suported for this use, it is reserved for the MAX function. Use the equivalent number instead, check this out: Equivalent of varchar(max) in MySQL?

edited May 23 '17 at 11:54

Community

1
1

answered Jun 07 '15 at 00:12

jotadepicas

2,389
2
26
48

score 1 · Answer 2 · answered Jun 07 '15 at 00:15

This will work for you. MAX is reserved keyword. Specify exact number of varchar instead of max. However, varchar(MAX) will work in SQL SERVER 2005+.

CREATE TABLE IF NOT EXISTS users (
  uuid varchar(36) NOT NULL,
  json varchar(21808) NOT NULL,
  PRIMARY KEY (uuid)
);

FIDDLE

score 1 · Answer 3 · answered Nov 10 '16 at 09:06

MAX() is a function in MySql,So if you want to declare the size to the max.please refer the below example.

CREATE TABLE IF NOT EXISTS users (
`uuid` varchar(36) NOT NULL,
`json` varchar(65535) NOT NULL,
PRIMARY KEY (`uuid`)
);

and if you calculate that 21845*3 = 65535, which wouldn't have worked anyway.
Whereas 21844*3 = 65532, which does work.

mysql> CREATE TABLE foo ( v VARCHAR(21844) ) CHARSET=utf8;
Query OK, 0 rows affected (0.32 sec)

Unknown syntax error in MySQL statement

3 Answers3

Linked