How to change data type when running query (not inside PHP)


Problem :

Explanation

When doing precision math on decimals inside MySQL, it handles strings as Float and not Fixed for example if we have following column in database (amount type is DECIMAL(43,20))

+--------------------------+
|         amount           |
+--------------------------+
|  0.20000000000000000000  |
+--------------------------+

running the following PHP code

$amount_to_add = "0.1";
$stmt = $dbh->prepare("UPDATE table SET amount = amount + :amount");
$stmt->bindValue("amount", $amount_to_add);
$stmt->execute();

would result this:

+--------------------------+
|         amount           |
+--------------------------+
|  0.30000000000000004000  |
+--------------------------+

notice the additional 0.00000000000000004000

its because $amount_to_add was string

As i need to use BC Math functions, its output is always string

And i can't convert it to double/float inside PHP because it will lose precision and decimal points

Questions

How should i pass this to MySQL to treat it as decimal(fixed) and not string (float) which doesn't require changing string type inside PHP?

Is something like declaring the datatype when running query possible? so it changes string to decimal inside MySQL



Solution :

UPDATE table SET amount = amount + CAST(:amount AS DECIMAL(X, Y))

Mysql Tutorials

Mysql Howto..

How do I delete the first 3 characters in mysql?

How to select all categories and count number of each category articles in MySQL

MySQL: How to bulk SELECT rows with multiple pairs in WHERE clause

how to calculate total number of saturday and sunday between two dates in mysql

How to save html file in MySQL DB using JDBC?

How to transform a normalised data set using PHP?

How to optimize MySQL Boolean Full-Text Search? (Or what to replace it with?) - C#

How do I re-format datetime in mysql?

MySQL: Storing a value from a field that may show up variable times on a form

How to convert PHP array into Mysql resource

How to select free rooms for reservations in CodeIgniter - two tables?

How to enable the MySQL BINARY operator in Hibernate queries and Session.get()

How to make a log while insert , update, delete a record into DataBase Tables in MYSQL Using PHP

how to change font in mysql database to store unicode charactors

How to get all possible combinations from a tag and item table?

How to output a subtotal after the last instance of an element of an array

How to collaborate on mysql schema?

How to save dynamic generated table data to mysql database in php codeigniter

how to connect to database on another server

How To Order A Hierarchical Table By Two Parameters In MySQL?

How to update table with subselect

How can I deny SELECT privelege to a single MySQL table?

How to order mysql query output from partial column data in 3 tables

How do I speed up (or break up) this MySQL query?

How to retrieve ID from other table for composite key in another table in mysql

How to convert rows to columns and query on them in Mysql

how to run mysql from the command line on mac?

How to send email notifications to registered users on a PHP/MySQL website [closed]

How to insert and retrieve at the same time in MySQL

In MySQL, how to convert this string to a date?