Do I really want to get rid of SQL_MODE?

I recently wrote about SQL_MODE, and Ronald Bradford was quick to reply with his view on things. Although my post was a bit of a rant, and I think it was a necessary rant, it seems that Ronald and I largely agree on the overall matter of things.

Now today WebYog implies that I and Ronald want to get rid of it and replace it with more strict, rigid checks on data entering MySQL. Tell you want, however much I would like that, I am not stupid, I realize that is not practical. Rather, I say sure, keep SQL_MODE, but make it possible to enforce it and check it, and do not allow just about anyone with access to the database to compromise the data in it by a single command.

WebYog also implies that this would break a lot of applications. I say that is not the case as I say you can still set it at anything you want, just that it is enforced!

Even better, as WebYog says that applications share a MySQL instance, they may have, probably do have, different requirements. OK. Read what I wrote: I suggest you set it and enforce it on the database level. Right?

WebYou then pops up this example. With a table like this:
CREATE TABLE `test`.`strict`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`col1` VARCHAR(10) NOT NULL,
`col2` VARCHAR(10),
PRIMARY KEY (`id`));
An insert like this would fail:
INSERT INTO `test`.`strict`(`col2`) VALUES (‘string’);
Yes it would. And that INSERT is plain silly. Why am I assuming a value without specifying it anywhere, nowhere in the INSERT statement, not in the table schema, I just assume that MySQL will figure out an appropriate value for me? And if you DO assume something, like the value of the column c2 should be set to an empty string when not specified, then why not set the default value for it in the create table statement? Like this:
CREATE TABLE `test`.`strict`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`col1` VARCHAR(10) NOT NULL,
`col2` VARCHAR(10) DEFAULT '',
PRIMARY KEY (`id`));

Or? And note that in this case, having done things right, you don't get a warning! (Yes, the INSERT on the original table actually issues a warning. Why would I want to have code that issues warnings. And if the user can leave the column out without specifying it, why is it NOT NULL).

Actually, this behavior shows another MySQL weirdo behavior. If I change the table definition above slightly, and allow NULL values in the col2 column, what do I get from an INSERT if i leave col2 out? The same INSERT as above. The column col2 has the same database. The INSERT still specifies no value for it. Now col2 will be set to NULL. And this is supposed to be coherent, consistent and easy to use?

Now, when the column col2 allows NULLS, MySQL does something with my schema also, it adds a DEFAULT NULL clause to col2. So that makes a bit more sense, if it wasn't for the fact that it doesn't add a DEFAULT '' when I specify NOT NULL.

Enough of weird MySQL behavior complaints, all database systems have these, to an extent. But it would be nice if MySQL was more in line with the rest of them, which SQL_MODE is on its way to do, but then MySQL should also be able to enforce it, if I wanted it to.

/Karlsson
PS Comments are welcome but before you fire off your tomatoes, read what I wrote.