Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing leading zeros of integers in MySQL database as INTEGER

Tags:

I need MySQL to store numbers in a integer field and maintain leading zeros. I cannot use the zerofill option as my current field is Bigint(16) and numbers can vary in amount of leading zeros. IE: 0001 - 0005, then 008 - 010 may need to be stored. I am not concerned about uniqueness of numbers (these aren't being used as IDs or anything) but I still need them to be stored preferably as INTS.

The issue using CHAR/VARCHAR and then typecasting the values as integers in PHP means that sorting results via queries leads to alphanumeric sorting, IE: SORT BY number ASC would produce

001 002 003 1 100 101 102 2 

Clearly not in numerical order, but in alphanumeric order, which isn't wanted.

Hoping for some clever workarounds :)

like image 868
Michael Avatar asked Jul 29 '11 12:07

Michael


People also ask

Can integer have leading zeros SQL?

Is there way to insert leading zeros to a integer datatype column? No, because leading zeros are meaningless on numeric data.

Is 0 an integer in MySQL?

Introduction to MySQL INT typeAn integer can be zero, positive, and negative. MySQL supports all standard SQL integer types INTEGER or INT and SMALLINT . In addition, MySQL provides TINYINT MEDIUMINT , and BIGINT as extensions to the SQL standard.

Does MySQL support integer?

MySQL supports all standard SQL numeric data types which include INTEGER, SMALLINT, DECIMAL, and NUMERIC.


1 Answers

Keep the numbers stored as integers.

Then use function LPAD() to show the numbers (left) padded with zeros:

SELECT LPAD( 14, 7, '0') AS padded;  | padded  | ----------- | 0000014 | 

If the number of zerofill characters is variable, add another column in the table with that (zerofill) length.

like image 77
ypercubeᵀᴹ Avatar answered Oct 13 '22 15:10

ypercubeᵀᴹ