Optimal mysql field type

Posted on 2004-11-10
Last Modified: 2012-06-21
Hi all,
I have a table where I need to control my id numbers by my self... no need for autoincrement.

My problem is, that some of the ID numbers HAVE TO start with a zero.. Example: 01099304
I can not use int for the fieldtype, cause that will set the value to 1099304, and varchar would make the database slow... Is there a more optimal fieldtype for this???

Best regards,
Question by:Crapman
    LVL 13

    Expert Comment

    try datatype as numeric

    Author Comment

    LVL 5

    Accepted Solution

    Numeric is just string storage of floating point data types, it will solve leading 0's but is probably not the best solution.

    Try using the ZEROFILL Option:


    For example, if you stored 1099304 it would be returned as 01099304 while being stored in the optimal integer data type.
    LVL 13

    Expert Comment

    sorry i was in ms sql
    i dont know it numeric field datatype in mysql :(
    Example: 01099304 (It is logically number)
    but when you use it only uses 1099304 removes the 0

    If you are planning to print it out as 0001099304 (if you have standart length in all)
    write a function that returns as string
    you can get the lenght of number and add the zeros before number and return function


    Author Comment

    Wohoo, eyeh8u's solution worked perfectly!! :)

    Thanks to both of you for your support...

    Featured Post

    Training Course: Java/J2EE and SOA

    This course will cover both core and advanced Java concepts like Database connectivity, Threads, Exception Handling, Collections, JSP, Servlets, XMLHandling, and more. You'll also learn various Java frameworks like Hibernate and Spring.

    Join & Write a Comment

    I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
    Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
    This video discusses moving either the default database or any database to a new volume.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now