Mysql – alter table (thesql) adding column with default value of space (‘ ‘)

default valueMySQL

On my Mac, using 5.5.11 MySQL Community Server (GPL)

alter table foobaz  add column (
extendedTitle varchar(3000) DEFAULT NULL,
clasindxIndent int,
spacedash1 enum (' ', '-', '1') default ' ',
plusSubs enum (' ', '+') default ' ');

works perfectly. On my Debian server using 5.1.49-3-log (Debian), it fails with

ERROR 1067 (42000): Invalid default value for 'spacedash1'

I don't understand the error, or why it works on my Mac and not on my server. Any hints or pointers greatly appreciated.

Pat

Best Answer

Either the rules have changed, or somebody missed something in compiler builds.

According to the MySQL Documentation on ENUM

For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown.

Value Index

NULL NULL

'' 0

'one' 1

'two' 2

'three' 3

An enumeration can have a maximum of 65,535 elements.

Trailing spaces are automatically deleted from ENUM member values in the table definition when a table is created.

You are using MySQL 5.1.49. That build probably errors out on seeing a one-character string with a blank.

MySQL 5.5 probably removes trailing spaces leaving an empty string.

Related Solutions

Mysql – How to set addtime(current_timestamp, ’15:00:00′) as MySQL timestamp column default value

Two things

Try using a trigger
Use DATETIME instead of TIMESTAMP

I ran this sample code:

use test
drop table if exists rolando;
create table rolando
(
    id int not null auto_increment primary key,
    num int not null,
    c1 datetime
);
delimiter $$
CREATE TRIGGER `rolando_15min`
BEFORE INSERT ON `rolando` FOR EACH ROW
BEGIN
    SET NEW.c1 = now() + INTERVAL 15 minute;
END;
$$
DELIMITER ;
INSERT INTO rolando (num) VALUES (4),(7);
SELECT SLEEP(5);
INSERT INTO rolando (num) VALUES (5),(9);
SELECT *,now() FROM rolando;

Here is the result:

mysql> use test
Database changed
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> create table rolando
    -> (
    ->     id int not null auto_increment primary key,
    ->     num int not null,
    ->     c1 datetime
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter $$
mysql> CREATE TRIGGER `rolando_15min`
    -> BEFORE INSERT ON `rolando` FOR EACH ROW
    -> BEGIN
    ->     SET NEW.c1 = now() + INTERVAL 15 minute;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> INSERT INTO rolando (num) VALUES (4),(7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.01 sec)

mysql> INSERT INTO rolando (num) VALUES (5),(9);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT *,now() FROM rolando;
+----+-----+---------------------+---------------------+
| id | num | c1                  | now()               |
+----+-----+---------------------+---------------------+
|  1 |   4 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  2 |   7 | 2011-09-02 04:05:56 | 2011-09-02 03:51:01 |
|  4 |   5 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
|  5 |   9 | 2011-09-02 04:06:01 | 2011-09-02 03:51:01 |
+----+-----+---------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Caveat : I tried at least 7 ways to make timestamp work like this. NO GO !!! So, you must use DATETIME.

Postgresql – How to create column in DB with default value random string

PostgreSQL example:

CREATE OR REPLACE FUNCTION f_random_text(
    length integer
)
RETURNS text AS
$body$
WITH chars AS (
    SELECT unnest(string_to_array('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 0 1 2 3 4 5 6 7 8 9', ' ')) AS _char
),
charlist AS
(
    SELECT _char FROM chars ORDER BY random() LIMIT $1
)
SELECT string_agg(_char, '')
FROM charlist
;
$body$
LANGUAGE sql;


DROP TABLE IF EXISTS tmp_test;


CREATE TEMPORARY TABLE tmp_test (
    id serial,
    data text default f_random_text(12)
);


INSERT INTO tmp_test
VALUES 
    (DEFAULT, DEFAULT),
    (DEFAULT, DEFAULT)
;


SELECT * FROM tmp_test;

 id |     data
----+--------------
  1 | RYMUJH4E0NIQ
  2 | 7U4029BOKAEJ
(2 rows)

Apparently you can do this. (Of course, you can add other characters as well, or use other random string generator as well - like this, for example.)

Best Answer

Related Solutions

Mysql – How to set addtime(current_timestamp, ’15:00:00′) as MySQL timestamp column default value

Postgresql – How to create column in DB with default value random string

Related Question