How to fill or fill a column with zeros by a MySQL query?

Question

I have a large table (~ 10,000) and I need one column to occupy exactly three spaces. It almost always occupies only one space, but I need the other two spaces to be filled with zeros (this is an integer column). Is there a function for this?

Answer 1

Add the ZEROFILL attribute to the int column.

Answer 2

Why would you do this? If this is due to the fact that you need some kind of report that queries the table in order to display single-valued values ​​in the form of three digits, add zero fill values ​​when it is displayed. Do not mix the displayed information in your data.

Answer 3

To add indentation when displaying data with select, you can use LPAD:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lpad

Answer 4

Leading zeros are not stored in INT columns.

You may need to add a column that stores the converted value of the row with leading zeros.

Answer 5

If you have control over the definition of the table in question, you can use the MySQL zerofill attribute . It allows you to define a numeric column filled with zeros.

If you don't have this control (and what you really should do anyway), add an add-on when querying values ​​from a table:

 SELECT LPAD(CONVERT(IntColumn,VARCHAR(3)),3,'0') 

Thus, the integer is stored as a normal integer, as it should be, but in terms of what this query consumes, the integers are padded .