Re: DBD::mysql retrieving column collation

From: Paul DuBois <paul(at)mysql.com>
Date: Thu Jul 05 2007 - 20:18:01 EDT

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/perl?unsub=lists@pantek.com

Received on Thu Jul 5 20:18:24 2007