how to ensure mysql column has 4 character

Question

how to ensure mysql column has 4 character ?

Due to using the MATCH function with minimum 4 char, i want my primary key which is a INT to be at least 4 char.

How can i do it ?

eg. 1 will be 0001 in sql table

You would have to use a varchar. `0001` as an int will always be `1` — Shmiddty, Jan 09 '13 at 17:58

score 1 · Accepted Answer · edited Aug 27 '13 at 07:27

1

Use ZEROFILL attribute of column. Alter your Primary key column to have zerofill enabled.

Try this:

ALTER TABLE `tablename` CHANGE
COLUMN `id` `id` INT(4) ZEROFILL NOT NULL AUTO_INCREMENT ;

edited Aug 27 '13 at 07:27

luiges90

answered Jan 09 '13 at 18:34

Saharsh Shah

score 0 · Answer 2 · answered Jan 09 '13 at 18:01

0

You can use MySQL's LPAD function:

SELECT LPAD('1',4,'0');
    -> '0001'

answered Jan 09 '13 at 18:01

Madbreaks

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

Assuming your primary key is an auto-incremented id.

Temporarily take the auto-increment off.
Add 999 to every id. (Of course if other tables use this id as a foreign key, you are going to have to adjust them, too).
Re-add the auto-increment, but make sure that it starts at at least 1000. See: How to set initial value and auto increment in MySQL?

edited May 23 '17 at 12:04

Community

answered Jan 09 '13 at 18:02

DWright

1

For any production, established relational database this probably isn't feasible. – Madbreaks Jan 09 '13 at 18:03
@Madbreaks. You're absolutely right. I'm inferring from the question that it probably is not production stuff. But if it is, forget what I said. – DWright Jan 09 '13 at 18:07
That sounds fine. If it's production data, you don't want to mess with the already existing keys. – DWright Jan 11 '13 at 18:07

3 Answers3