From: Jeremy Cole
Date: November 5 2007 6:24pm
Subject: Re: SHOW CREATE TABLE and auto_increment
List-Archive: http://lists.mysql.com/internals/35158
Message-Id: <472F5FEB.30406@provenscaling.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Howdy,

Rick James wrote:
> 1. looks to me like the it does NOT differ from the default??

It does actually.  Note the following:

VARCHAR(10)
   == VARCHAR(10) CHARACTER SET <table default> COLLATE <table default>

However:

VARCHAR(10) CHARACTER SET <charset>
   == VARCHAR(10) CHARACTER SET <charset> COLLATE <charset default>

Note the *charset default* not *table default*.  That is to say that 
"CHARACTER SET latin1" is equivalent to "CHARACTER SET latin1 COLLATE 
latin1_swedish_ci", as latin1_swedish_ci is the default collation for 
latin1.

As for how much sense this makes, I can see both sides of the issue. 
Not sure offhand what the SQL standard says, if anything.

> 2. What good is COLLATE on the CREATE TABLE if it is totally ignored by
> the column?

It's not really ignored per se, it's the inclusion of the "CHARACTER SET 
xxx" and its behaviour that causes this.  To make matters worse, the 
output of SHOW CREATE TABLE is fairly ambiguous.

Regards,

Jeremy

-- 
high performance mysql consulting
www.provenscaling.com