Converting CHAR to BIT

Question

I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows:

• 16 characters long string
• 10 characters long string
• blank value

Now, I want to convert blank and 10 characters long string to 0 and 16 characters string to 1. But table is quite large so I think I should avoid any experiment and pursue the best approach.

First, I thought to create and index then update the column and then change data type.

Answer 1

Can you be more specific on "quite large"? In general you're right, you shouldn't just 'do it live' in production with any change. Do you have any kind of QA or test environment you can do a run on first?

This isn't the most sexy ninja one line approach but you could

• Add a new bit column
• Issue updates setting the new column = if( length(old_column)=16,1,0)
• Alter table to drop old column and rename the new column

Answer 2

I have an interesting idea. How about letting mysql decide the best datatype ???

Here is an example using PROCEDURE ANALYSE()

Also, I have some sample data of lengths 0,10, and 16

use test
drop table if exists worktable;
create table worktable
(
    id int not null auto_increment,
    chdata varchar(16),
    primary key (id)
) ENGINE=MyISAM;
insert into worktable (chdata) values
(''),('0123456789'),('0123456789ABCDEF'),
(''),('0123456789'),('0123456789ABCDEF'),
(''),('0123456789'),('0123456789ABCDEF'),
(''),('0123456789'),('0123456789ABCDEF');
select * from worktable;
select chdata from worktable procedure analyse()\G

Here is the execution of my example in MySQL 5.5.12 for Windows

mysql> use test
Database changed
mysql> drop table if exists worktable;
Query OK, 0 rows affected (0.01 sec)

mysql> create table worktable
    -> (
    ->     id int not null auto_increment,
    ->     chdata varchar(16),
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into worktable (chdata) values
    -> (''),('0123456789'),('0123456789ABCDEF'),
    -> (''),('0123456789'),('0123456789ABCDEF'),
    -> (''),('0123456789'),('0123456789ABCDEF'),
    -> (''),('0123456789'),('0123456789ABCDEF');
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from worktable;
+----+------------------+
| id | chdata           |
+----+------------------+
|  1 |                  |
|  2 | 0123456789       |
|  3 | 0123456789ABCDEF |
|  4 |                  |
|  5 | 0123456789       |
|  6 | 0123456789ABCDEF |
|  7 |                  |
|  8 | 0123456789       |
|  9 | 0123456789ABCDEF |
| 10 |                  |
| 11 | 0123456789       |
| 12 | 0123456789ABCDEF |
+----+------------------+
12 rows in set (0.00 sec)

mysql> select chdata from worktable procedure analyse()\G
*************************** 1. row ***************************
             Field_name: test.worktable.chdata
              Min_value:
              Max_value: 0123456789ABCDEF
             Min_length: 0
             Max_length: 16
       Empties_or_zeros: 4
                  Nulls: 0
Avg_value_or_avg_length: 8.6667
                    Std: NULL
      Optimal_fieldtype: ENUM('','0123456789','0123456789ABCDEF') NOT NULL
1 row in set (0.00 sec)

See how mysql decided to create an ENUM based on current values? You definitely need to use the enum type or tinyint. Personally, I hate having to deal with bit field types.

Personal feelings aside, since you want to use bit fields, let's transform all strings that are 16 characters to 1 and all others to 0. Here is what you can do to transform the chdata to a bit field

drop table if exists worktable_new;
create table worktable_new like worktable;
alter table worktable_new modify column chdata bit(1);
insert into worktable_new (id,chdata)
select id,if(length(chdata)=16,1,0) from worktable;
select * from worktable_new;
select count(1),chdata from worktable_new group by chdata;

Using the same sample data, here is the transformation

mysql> drop table if exists worktable_new;
Query OK, 0 rows affected (0.00 sec)

mysql> create table worktable_new like worktable;
Query OK, 0 rows affected (0.04 sec)

mysql> alter table worktable_new modify column chdata bit(1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into worktable_new (id,chdata)
    -> select id,if(length(chdata)=16,1,0) from worktable;
Query OK, 12 rows affected (0.00 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from worktable_new;
+----+--------+
| id | chdata |
+----+--------+
|  1 |        |
|  2 |        |
|  3 |       |
|  4 |        |
|  5 |        |
|  6 |       |
|  7 |        |
|  8 |        |
|  9 |       |
| 10 |        |
| 11 |        |
| 12 |       |
+----+--------+
12 rows in set (0.00 sec)

mysql> select count(1),chdata from worktable_new group by chdata;
+----------+--------+
| count(1) | chdata |
+----------+--------+
|        8 |        |
|        4 |       |
+----------+--------+
2 rows in set (0.00 sec)

Although not visible to the naked eye, the 4 rows that had 16 characters are now bit(1) and all other rows are bit(0).

From here you can get rid of the old table and keep the new like this

mysql> alter table worktable rename worktable_old;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table worktable_new rename worktable;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table worktable_old;
Query OK, 0 rows affected (0.00 sec)

mysql>

Give it a Try !!!

Answer 3

It seems that at the moment you don't have and index on that column then I think your first considered approach is quite good but instead of changing current column values and then changing its type instead create a new column with bit data type and set its values checking the length or if its same string then by exact match after that delete the old column.