What is the significance of the "UNSIGNED ZEROFILL" attribute in MySQL and how can it be utilized for storing numbers with leading zeros?

The "UNSIGNED ZEROFILL" attribute in MySQL is used to store numbers with leading zeros, ensuring that the specified number of digits is always displayed, even if the actual number is smaller. This is particularly useful for storing data such as postal codes or identification numbers where leading zeros are significant. 

// Create a table with a column using UNSIGNED ZEROFILL attribute
$sql = "CREATE TABLE users (
    id INT(5) UNSIGNED ZEROFILL PRIMARY KEY,
    name VARCHAR(50)
)";
mysqli_query($conn, $sql);

// Insert data with leading zeros
$sql = "INSERT INTO users (id, name) VALUES ('00123', 'John Doe')";
mysqli_query($conn, $sql);

// Retrieve data with leading zeros
$sql = "SELECT id, name FROM users";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row['id'] . " | Name: " . $row['name'] . "<br>";
}

Keywords

MySQL UNSIGNED ZEROFILL attribute numbers leading zeros

Related Questions