Create/alter Table Default Empty String

[mhirons]

When I use the GUI to Create/Alter a table, is there a way to set the default value of a varchar field to the empty string?

In other words, I want it to produce this sql:

[codebox]CREATE TABLE `blah` (

`partnum` varchar(20) NOT NULL default '',

PRIMARY KEY (`partnum`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1[/codebox]

If I leave the default blank in the GUI window, it doesn't use any default value, like this:

[codebox]CREATE TABLE `blah` (

`partnum` varchar(20) NOT NULL,

PRIMARY KEY (`partnum`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1[/codebox]

If I put '' (two single quotes) as the default, I get this:

[codebox]CREATE TABLE `blah` (

`partnum` varchar(20) NOT NULL default '''''',

PRIMARY KEY (`partnum`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1[/codebox]

[peterlaursen]

No … I cannot either figure out any way to do this.

And very strange actually that we never considered this and nobody else (as far as I remember) ever requested this.

I cannot either 'right out of my head' find a good way to implement. Do you have any good idea?

[mhirons]

peterlaursen wrote on Jul 11 2007, 07:51 PM:

I cannot either 'right out of my head' find a good way to implement. Do you have any good idea?

I can think of two things, but they each have drawbacks…

The first would be something similar to how you can right-click on the table data grid and choose “Set to NULL”. The special string (NULL) is put in the cell, and gets sent to the database as NULL. We could have another special string, (EMPTY), that would get sent to the database as an empty string. If you really did want the default to be (EMPTY), you could enclose it with back quotes, `(EMPTY)`.

The second idea would be to get rid of the automatic quoting/escaping and force the user to enter in exactly what is sent to the database. In other words, to set the default string to test, you would have to enter 'test', including the single quotes. To set the default string to can't, you would have to enter 'can't'. To set the default to an empty string, you specify ''. The downside to this is that it may be confusing to some users.

I'll try to look at a couple other database GUI programs to see how they handle this situation.

[mhirons]

MySQL Administrator uses my 2nd suggestion, it forces you to enter the default string enclosed in quotes and escaped.

It looks like the SQL Server GUI does something similar. Your default value needs to be enclosed in quotes and escaped, but it also allows you to put whatever you want and then when the cell loses focus, it automatically makes sure your value is enclosed in single quotes and does any necessary escaping. So, if you type in don't, it replaces it with 'don''t'. If you type in 'test', it leaves it alone. That's a pretty good idea, it gives you both flexibility and ease of use.

[peterlaursen]

I don't like either solutions!

The SQLyog concept is the opposite of what MA does. In SQLyog you enter what shall be stored and you see what is stored! That means that escaping, doubling '' with LIKE and charset conversion are all done in the backgorund. User shall not need to worry about that!

The SQL server client solution is better .. at least if it was a configuration option. But is is a terrible lot of code changes if we should implement that I think.

I favour implementing the (EMPTY) keyword in CREATE/ALTER table DEFAULT column of the grid (and a context menu).

But I have asked collegues to think … (and believe it or not .. I had success with that before! <_< )

[JimZ]

Funny… I searched the forums with the same question and found this thread, only a week old.

If you use a keyword, then no matter what you decide, with you _will_ need an escape mechanism, or else you end up with an inconsistency. If you want to use the keyword EMPTY, then users have no way of entering a default of the actual text string 'EMPTY'.

And if you use a keyword, why not use the keyword NULL? When someone creates a new column (or changes the column datatype), automatically populate the column with the word NULL. And use some escape mechanism such as NULL or similar to actually indicate the string 'NULL'.

Currently, you seem to have conflicting behavior. I see an empty column under default, yet I end up with NULL as the default. That doesn't make sense to me.

[peterlaursen]

1) @JimZ: please read this FAQ.

http://webyog.com/faq/8_99_en.html

The 'escape mechanism' that we use in SQLyog GUI to distinguish between a KEYWORD and a LITERAL is `backticks`. “NULL” is the keyword NULL – “`NULL`” is the literal string 'NULL'.

2) accordingly we will release version 6.05 soon (probably tommorrow) where the string ” '' ” (two singlequotes) will be treated like a KEYWORD is this special context only: the DEFAULT column of CREATE/ALTER TABLE. To set default literally ” '' ” you enter ” `''` ” (two singlequotes surrounded with backticks).

[peterlaursen]

We will not be able to include this in version 6.05. The reason is that when a column is declared NOT NULL different MySQL versions behave differently with defaults. It was too late we became aware of that, and we will not postpone 6.05 for this reason.

It is still a high-priority 'fix' and it will be added soon!

[Author]

Posts