MySQL Syntax Error using phpMyAdmin

nexgen_x

When I try and save a new table using phpMyAdmin, I get the following error:

Error
SQL-query : [Edit]

CREATE TABLE profile (pid TINYINT NOT NULL AUTO_INCREMENT, name VARCHAR NOT NULL, gender VARCHAR NOT NULL, race VARCHAR NOT NULL, classtype VARCHAR NOT NULL, disc VARCHAR NOT NULL, age TINYINT NOT NULL, guild VARCHAR NOT NULL, hrace LONGTEXT NOT NULL, behavior LONGTEXT NOT NULL, fears LONGTEXT NOT NULL, goals LONGTEXT NOT NULL , PRIMARY KEY (pid))

MySQL said:

You have an error in your SQL syntax near 'NOT NULL, gender VARCHAR NOT NULL, race VARCHAR NOT NULL, classtype VARCHA' at line 1

Any ideas?

Cheers.

nexgen_x

/fixed it by changing the VARCHARS to MEDIUMTEXT

But no idea why that worked - can anyone explain?

ta.

jeffwhite

You have to specifying length (how many characters) for a VARCHAR field.

nexgen_x

ah right, thanks.

Does using VARCHARS have any specific advantages? Otherwise I'll stick with the easier choice 😃

jeffwhite

Storage size
VARCHAR(50) with 1 letter stored uses 1 byte + 1 byte for overhead and can't go over 50 characters or 51 bytes for storage

MEDIUMTEXT with 1 letter stored uses 1 byte for each character + 3 bytes for overhead with a 16777215 character limit. So you can see using MEDIUMTEXT opens you up to triple the overhead and potential for a 16+M storage problem.

It takes deciding "how big can this data be?" up front. Example: a social secutity number is always 9 characters so use VARCHAR(9)
How big can a name be? VARCHAR(16) will work for about 99.9% of last names.
zip codes, phone numbers, etc are easy.

VARCHARs can only go up to 255 characters
TEXT can do 64K
TINYTEXT is the same as VARCHAR(255)

Good Luck,
Jeff