Specify a default of 225 spaces for a varchar column

Question

I am trying to save a 15 by 15 letters board for a word game as a 225 characters string in MySQL 5.6 database by using the following table:

create table games (
        gid integer primary key auto_increment,
        player1 integer references users(uid) on delete cascade,
        player2 integer references users(uid) on delete cascade,
        stamp1 integer not null default 0,
        stamp2 integer not null default 0,
        letters1 varchar(7) not null,
        letters2 varchar(7) not null,
        letters varchar(116) not null,
        board varchar(225) not null default space(225)
);

Unfortunately, this returns an 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 'space(225))'

I am just trying to initialize the game board with 15 x 15 spaces - and would like to modify that board with each move later.

Could you please recommend me a better way for doing that?

could use char(255) instead, which creates a fixed-size column. See http://dev.mysql.com/doc/refman/5.6/en/char.html — DBug, Dec 08 '15 at 15:32
Yes. Can't make out what you have for default, but mysql will pad value with spaces up to 255 when inserting and strip the spaces when retrieving (you can have it leave the spaces, see link above) — DBug, Dec 08 '15 at 15:38
`The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.`. See [13.1.17 CREATE TABLE Syntax](https://dev.mysql.com/doc/refman/5.6/en/create-table.html). — wchiquito, Dec 08 '15 at 15:41

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

You cannot use a function as a default value:

Can I use a function for a default value in MySql?

so you can just define your default values as a string:

board varchar(225) not null default ".................up to 225...."

or you can use a trigger.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 08 '15 at 15:42

fthiella

48,073
15
90
106

I see thanks... I am just uneasy with writing out such a long string in my `database_creation.sql` (because it would be difficult to spot a missing whitespace). I wish I could say `' ' x 225` like in Perl – Alexander Farber Dec 08 '15 at 15:44
1

@AlexanderFarber I agree, a function like SPACE(225) is very clear, while a sequence of spaces is not... but I don't think there's any other solution, only a trigger. – fthiella Dec 08 '15 at 15:46

Specify a default of 225 spaces for a varchar column

1 Answers1