Re: DBD::mysql retrieving column collation

At 1:08 PM +0100 7/2/07, John ORourke wrote:
>Hi folks,
>
>I've written a schema sync script which allows me to make schema 
>updates on multiple servers without disturbing data, and I've just 
>added the ability to check column types but can't see what the 
>collation type is from a DBD::mysql statement handle!
>
>Is it possible to retrieve a column's collation from a statement handle?
>
>I need to know if a column is binary or not - eg. 'varchar(255) 
>binary' or just 'varchar(255)'
>
>I'm about to resort to parsing the output of  'show create table' as 
>I can't see any other way!

In the C API, you can distinguish binary from non-binary strings by
checking whether the charset_nr value is 63 (binary) or <> 63 (non-binary).
However, I don't think that DBD::mysql exposes this member of the column
metadata structures.

If you resort to parsing SHOW output, you might find the output of
SHOW COLUMNS easier to deal with, because it's in row-and-column
format.  SHOW FULL COLUMNS displays an additional Collation column,
which is non-NULL for non-binary string columns.  (For binary string
data types such as BINARY or VARBINARY or BLOB, it's NULL.)

Example:

mysql> show create table t\G
*************************** 1. row ***************************
        Table: t
Create Table: CREATE TABLE `t` (
   `c1` char(10) DEFAULT NULL,
   `c2` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
   `c3` binary(10) DEFAULT NULL,
   `t` text,
   `b` blob
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show full columns from t\G
*************************** 1. row ***************************
      Field: c1
       Type: char(10)
  Collation: latin1_swedish_ci
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
*************************** 2. row ***************************
      Field: c2
       Type: char(10)
  Collation: latin1_bin
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
*************************** 3. row ***************************
      Field: c3
       Type: binary(10)
  Collation: NULL
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
*************************** 4. row ***************************
      Field: t
       Type: text
  Collation: latin1_swedish_ci
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
*************************** 5. row ***************************
      Field: b
       Type: blob
  Collation: NULL
       Null: YES
        Key:
    Default: NULL
      Extra:
Privileges: select,insert,update,references
    Comment:
5 rows in set (0.05 sec)

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]