MySQL/MariaDB: Returning the right types

[Anonymous]

As PHP is getting more type-conscious and generally:

E.g. fetch_assoc returns numeric values as strings, despite the columns being set up with numeric and other types. I'm careful to explicitly cast to appropriate types in my code, so effectively it should all work as expected, but it adds an unnecessary and error-prone step.

I could use field_type to cast to the right types automatically and generally, but that would mean numeric values are stored as such, returned as strings, and then again converted into numeric values, which is a waste of resources and also a potential cause of conversion errors.

I noted that mysqlnd (a supposed solution) is in fact loaded, based on advice from https://stackoverflow.com/questions/20079320/php-pdo-mysql-how-do-i-return-integer-and-numeric-columns-from-mysql-as-int, but it makes no difference. I still get only strings.

So, is there a sure-fire way to get the right types in SELECT responses without any casts anywhere, with or without mysqlnd.

I use MariaDB and mysqli.

As I'm on the topic:

Also, MySQL/MariaDB has no proper boolean type (it's just a TINYINT), which I consider a given, and would enable returning such values as native PHP (or any other language) boolean. It's also a pain to insert and get 0 and 1, when I clearly mean false and true. Is there any way for laymen to provide such feedback to the PHP, MySQL and MariaDB teams?

Thanks in advance.

Update:

This is what phpinfo says about mysqlnd. Does it need to be activated too?

mysqlnd
mysqlnd enabled
Version mysqlnd 7.4.4
Compression supported
core SSL supported
extended SSL supported
Command buffer size 4096
Read buffer size 32768
Read timeout 86400
Collecting statistics Yes
Collecting memory statistics No
Tracing n/a
Loaded plugins mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password
API Extensions mysqli,pdo_mysql

 

[Anonymous]

error-prone step

Do you have a specific example?

So, is there a sure-fire way to get the right types in SELECT responses without any casts anywhere, with or without mysqlnd.

If you mean when it is sent to PHP, then no, you will need to cast any values.

Is there any way for laymen to provide such feedback to the PHP, MySQL and MariaDB teams?

Yes, join their mailing lists and follow their "how can you get involved" links.

Before doing that however, you probably won't be the first to ask so do some research before asking and also note that TRUE and FALSE are keywords.

However, MySQL stores that data pretty much as you have requested, it passes the values to PHP, which deals with them as needed, when PHP passes values to MySQL, any conversion needed is done by them (I'm not sure which one does it) as specified in your database schema.

Do you really need to be that concerned with datatypes in your script? Many will argue that you should strongly type data, and many will argue against it, the idea with PHP was that the programmer should not have to worry about datatypes for various reasons, personally I'm in favour of strongly typing data, but I can embrace PHPs view of it since it revolves around sending textual versions to the user meaning it would need converting before doing so. PHP automatically and seamlessly does this conversion for you.

Do you have a specific problem you are faced with?