Problem with CHARSET in MySQL create table

Question

Hi this is my code and it returns "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 'VALUE, chapter VARCHAR(100) CHARACTER SET utf8 COLLA' at line 2"

CREATE TABLE IF NOT EXISTS texts (
id SERIAL DEFAULT VALUE,
chapter VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci,
text LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci,
uid INT NOT NULL AUTO_INCREMENT UNIQUE KEY,
time TIMESTAMP,
FULLTEXT (chapter)
)Engine = InnoDB;

I have no idea what is wrong, I was writing it according to MySQL documentation.

I also tried changing this column to

chapter VARCHAR(100) CHARACTER SET utf8, ...

but it returned the same error.

Answer 1

The line:

id SERIAL DEFAULT VALUE,

is invalid. You need to specify a value for DEFAULT VALUE or take it out.

id SERIAL,

However you have 2 auto_increment fields in your table which is also invalid. Serial is just an alias for

BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT .

Answer 2

Thank you, that's it. But I wonder what means this? It says SERIAL DEFAULT VALUE in the definition of an integer column is an alias for NOT NULL AUTO_INCREMENT UNIQUE. So why SERIAL DEFAULT VALUE is invalid?