How to fix "A comma or a closing bracket was expected"?-CodePudding

I get "A comma or a closing bracket was expected" error when trying to execute this SQL code:

CREATE TABLE `player_vehicles` (
  `#` int(11) NOT NULL,
  `steam` varchar(50) DEFAULT NULL,
  `citizenid` varchar(50) DEFAULT NULL,
  `vehicle` varchar(50) DEFAULT NULL,
  `hash` varchar(50) DEFAULT NULL,
  `mods` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `plate` varchar(50) NOT NULL,
  `fakeplate` varchar(50) DEFAULT NULL,
  `garage` varchar(50) DEFAULT NULL,
  `fuel` int(11) DEFAULT 100,
  `engine` float DEFAULT 1000,
  `body` float DEFAULT 1000,
  `state` int(11) DEFAULT 1,
  `depotprice` int(11) NOT NULL DEFAULT 0,
  `drivingdistance` int(50) DEFAULT NULL,
  `status` text DEFAULT NULL,
  `health` longtext NOT NULL '[{"value":100,"part":"electronics"},{"value":100,"part":"fuelinjector"},{"value":100,"part":"brakes"},
{"value":100,"part":"radiator"},{"value":100,"part":"driveshaft"},{"value":100,"part":"transmission"},{"value":100,"part":"clutch"}]'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Error message:

A comma or a closing bracket was expected. (near "'[{"value":100,"part":"electronics"},{"value":100,"part":"fuelinjector"},{"value":100,"part":"brakes"},{"value":100,"part":"radiator"},{"value":100,"part":"driveshaft"},{"value":100,"part":"transmission"},{"value":100,"part":"clutch"}]'" at position 816)

Can someone please help, I can't figure this out?

CodePudding user response：

It seems like you want to use a string as a default for your longtext column, but you are missing the DEFAULT keyword.

But it won't work anyway. https://dev.mysql.com/doc/refman/8.0/en/blob.html says:

BLOB and TEXT columns cannot have DEFAULT values.

If I add the DEFAULT keyword to your example, and try it in the MySQL client, I get this error:

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'health' can't have a default value

MariaDB, a fork of MySQL, added the capability to add a DEFAULT value to a BLOB or TEXT column in MariaDB 10.2.1, but this is not supported in MySQL.

CodePudding user response：

If you need to assign some default value to LONGTEXT column then use BEFORE INSERT trigger.

CREATE TABLE test (id INT, txt LONGTEXT NOT NULL);
CREATE TRIGGER tr_bi_setdef
BEFORE INSERT ON test
FOR EACH ROW
SET NEW.txt = COALESCE(NEW.txt, '["default value"]');
INSERT INTO test VALUES (1, '["specified value"]'), (2, NULL);
INSERT INTO test (id) VALUES (3);
SELECT * FROM test;

id | txt                
-: | :------------------
 1 | ["specified value"]
 2 | ["default value"]  
 3 | ["default value"]

db<>fiddle here