Setting scale and precision for MySQL float column

[cxj]

I believe I've found an undocumented way to correctly set the float(scale,precision) column type for MySQL using Phinx, but before I make a PR to add it to the documentation, I figured I'd better ask here if this is intended correct behavior, or lucky happenstance.

Example

I have a column with definition float(3,2) and I need to expand it to be float(4,2).

The following appears to work, but is undocumented in both the Phinx and CakePHP documentation.

public function up(): void
{
    $this->table('my_table')
        ->changeColumn('my_column', 'float', ['scale' => 2, 'precision' => 4])
        ->save();
}

[dereuromark]

Refs open cakephp/migrations#314 and cakephp/docs#5871

[garas]

Also note that as of MySQL 8.0.17, the nonstandard FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated and you should expect support for it to be removed in a future version of MySQL.

Depending on usage, you should use DECIMAL(M,D) to store exact numeric data values when it is important to preserve exact precision, for example with monetary data.

If you need store approximate numeric data values, use FLOAT without precision, and format number to necessary precision in application code instead.

[dereuromark]

Postgres is actually already failing on this:

LINE 1: ... "sort" INTEGER NOT NULL  DEFAULT '0', "lat" REAL (10) NULL,...

So, yeah, scale and precision should be omitted for float, or decimal should be used.

We should probably clarify in docs.