CODEWITHSUNDEEP
mysql
Arwen
Arwen

Reputation: 205

What data type should I use to store U.S States inside MySQL databse?

Here is the code: 

<div class="control-group questions">
    <div class="field-control randomclass-two">
        <input class="form-control" name="zipcode" id="zipcode" type="zip" placeholder="Zipcode" required="" data-validation-required-message=" " />
            <div class="second-selection"><select  name="statetwo" id="second-opt">
<option>State</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>

I looked into the MySQL manual but did not find anything that would explain to me how to do it.

Upvotes: 0

Views: 1040

Answers (1)

Eric J.
Eric J.

Reputation: 150238

You could encode all 88 state codes in 1 byte (256 possible states from 8 bits). However, that would require a lookup table (1=AL, 2=AK, ...)

If you encode the states with two bytes, you avoid the need for a lookup table. For most applications, the difference between using 1 or 2 bytes to encode the state will have a negligible impact on performance, storage requirements or cost.

Since the state codes are standardized, I would opt for CHAR(2). I would sacrifice a small amount of extra storage for simplicity.

Upvotes: 1

Related Questions