Initial zeros stripped of when using Max in mysql

Question

To test -

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer CHAR(20) DEFAULT '' NOT NULL,
 price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
 (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
 (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

My Query -

SELECT MAX( article ) AS article
FROM shop

It outputs -

+---------+
| article |
+---------+
| 4 |
+---------+

However I was expecting -

+---------+
| article |
+---------+
| 0004 |
+---------+

I tried to CAST but no luck.

Let me know how could I handle this.

FYI- I tried to create SQL Fiddle for this but fiddle is not filling Zeros http://sqlfiddle.com/#!2/4abbe/24 when executed SELECT * FROM shop

However in phpmyadmin its working correctly with ooo's fill.

MySQL setting - Server version: 5.5.16 PHPMYADMIN setting - Version information: 3.4.5

My table data representation -

table data

• Damien_The_Unbeliever: If leading 0s are significant for your data, it doesn't sound like INT is the right data type to use.
• swapnesh: @Damien_The_Unbeliever then should i store it in VARCHAR or any other for leading zeros? your suggestion ?

Answer 1

SELECT LPAD(MAX( article ), 4, '0') AS article FROM shop; 

;-)

• medina: Hey, mate. I know that you mean. =/

Answer 2

You can use LPAD().

SELECT LPAD(article, 4, 0) FROM table_name;

Answer 3

When you are using ZEROFILL it is actually storing it in database in that form but while retrieving it truncates the zero. So you should use:-

SELECT LPAD(MAX( article ), 4, '0') AS article FROM shop;