在数据库查询中使用逗号作为小数分隔符

Question

I've set Swedish locale to use comma as decimal separator. For example it should be 123,45 instead of 123.45.

To test it:

echo 100/3;

It gives desirable result:

33,333333333333 

However when fetching or inserting data in the database using PHP PDO, it does not seem to work. For if I try to update a table row:

$sql = "UPDATE
            table_name
        SET
            column_name = 123,45 //does not work but 123.45 works.
        WHERE   
            row_id = 1
    ";  
$q = $db->prepare( $sql );
$q -> execute();

In the above code I get error if I try to use a comma separator eg. 123,45 but it works if I use dot. Similarly when I select the data

$sql = "SELECT
            column_name
        FROM
            table_name
        WHERE   
            row_id = 1
    ";  

    $q = $db->prepare( $sql );
    $q -> execute();
    $test = $q -> fetch( PDO::FETCH_COLUMN );
    var_dump($test); //string "123.45" 

It will return the result with dot as a decimal separator. ie. 123.45 while I would like 123,45.

What I'm doing wrong? Edit: The column data type is numeric with scale 2.

Answer 1

This would be impossible for MySQL to handle because the comma is also the separator character in a list. Take for example the following query:

INSERT INTO table (a,b) VALUES(123,45,67)

What does this mean? Are the values 123 and 45.67? Or 123.45 and 67? Could be either one. It would be totally ambiguous.

I would suggest doing all your business logic in PHP using en_US and changing the locale to Swedish only for output.