there seems to be no difference between CHAR and VARCHAR

[rick645]

$connection = new PDO(
    "mysql:dbname=$dbName;host=db",
    "root",
    "root"
);
$statement = $connection->query(
    "SELECT column_name as 'Column Name', data_type as 'Data Type', character_maximum_length as 'Max Length'
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE
         TABLE_NAME   = '$tableReferences' AND
         COLUMN_NAME  = 'url'"
);
var_dump($statement->fetchAll(PDO::FETCH_ASSOC));

Output

string(37) "mysql:dbname=***;host=db"
array(1) {
  [0]=>
  array(3) {
    ["Column Name"]=>
    string(3) "url"
    ["Data Type"]=>
    string(4) "char"
    ["Max Length"]=>
    int(5)
  }
}

So we have the url field.
This field is of type CHAR (not to be confused with VARCHAR), i.e. it has a fixed length.
The length is equal to 5.

So let's do some testing

$statement = $connection->prepare("SELECT * FROM $tableReferences");
$statement->execute();
foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
    printf(
        "%s: Test result: %s\n",
        $row['url'] ,
        strlen($row['url']) == 5 ? "PASS (is equal to 5)" : "FAIL"
    );
};

Output

a: Test result: FAIL
ab: Test result: FAIL
abc: Test result: FAIL
abcd: Test result: FAIL
abcde: Test result: PASS (is equal to 5)

Why only the field of the last record passes the test?
They should not all have length 5?

Edited March 11, 2023 by rick645

[Barand]

Some bedtime reading for you...

https://dev.mysql.com/doc/refman/8.0/en/char.html

P.S.

The main difference is in the storage. Your code is demonstrating this particular feature stated in the above link

Quote

If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval.

Another example for you

$pdo->exec("DROP TABLE IF EXISTS testchar");
$pdo->exec("CREATE TABLE testchar ( col1 char(5), col2 varchar(5))");
$pdo->exec("INSERT INTO testchar (col1, col2) VALUES 
            ('a    ', 'a    '), 
            ('ab   ', 'ab   '), 
            ('abc  ', 'abc  '), 
            ('abcd ', 'abcd '), 
            ('abcde', 'abcde')
            ");

$res = $pdo->query("SELECT col1, col2 FROM testchar");
echo '<pre>';
foreach ($res as $r) {
    printf (" '%s' (%d)  |  '%s' (%d)<br>", $r['col1'], strlen($r['col1']), $r['col2'], strlen($r['col2']));
}

which outputs...

 'a' (1)  |  'a    ' (5)
 'ab' (2)  |  'ab   ' (5)
 'abc' (3)  |  'abc  ' (5)
 'abcd' (4)  |  'abcd ' (5)
 'abcde' (5)  |  'abcde' (5)

 

[rick645]

Interesting!!!
But when is it actually worth using CHAR vs VARCHAR?

[Barand]

Use varchar for columns which can have values of variable lengths (names, address lines, descriptions, comments etc)

Use char when you have values of fixed length (EG US state abbreviations could be CHAR(2), or if you have a product table that has a 4-character product code then CHAR(4)).

If in doubt use varchar, but don't go mad and make everything VARCHAR(255) as some do. Keep them as sensible size for the expected content. You don't want people entering a phone number with 255 digits just because your DB allows it.

[rick645]

Thanks for the info