Viewed   62 times

In reading "High performance MySQL" from O'Reilly I've stumbled upon the following

Another common garbage query is SET NAMES UTF8, which is the wrong way to do things anyway (it does not change the client library's character set; it affects only the server).

I'm a bit confused, because I used to put "SET NAMES utf8" on the top of every script to let the db know that my queries are utf8 encoded.

Can anyone comment the above quote, or, to put it more formally, what are your suggestions / best practices to ensure that my database workflow is unicode-aware.

My target languages are php and python if this is relevant.



mysql_set_charset() would be an option - but an option limited to the ext/mysql. For ext/mysqli it is mysqli_set_charset and for PDO::mysql you need to specify a connection parameter.

As using this function results in a MySQL API call, it should be considered much faster than issuing a query.

In respect of performance the fastest way to ensure a UTF-8-based communiction between your script and the MySQL server is setting up the MySQL server correctly. As SET NAMES x is equivalent to

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

whereas SET character_set_connection = x internally also executes SET collation_connection = <<default_collation_of_character_set_x>> you can also set these server variables statically in your my.ini/cnf.

Please be aware of possible problems with other applications running on the same MySQL server instance and requiring some other character set.

Sunday, October 30, 2022

Using PDO prepared statements:

$placeholders = str_repeat('?, ', count($array)-1) . '?';
$stmt = $pdo->prepare("SELECT * FROM table WHERE field IN ($placeholders)");

$placeholders will contain a sequence of ?, ?, ? placeholders, with the same number of ? as the size of the array. Then when you execute the statement, the array values are bound to the placeholders.

Saturday, August 27, 2022

I typically create a header filesystem.hpp with the following content:

// We haven't checked which filesystem to include yet

// Check for feature test macro for <filesystem>
#   if defined(__cpp_lib_filesystem)

// Check for feature test macro for <experimental/filesystem>
#   elif defined(__cpp_lib_experimental_filesystem)

// We can't check if headers exist...
// Let's assume experimental to be safe
#   elif !defined(__has_include)

// Check if the header "<filesystem>" exists
#   elif __has_include(<filesystem>)

// If we're compiling on Visual Studio and are not compiling with C++17, we need to use experimental
#       ifdef _MSC_VER

// Check and include header that defines "_HAS_CXX17"
#           if __has_include(<yvals_core.h>)
#               include <yvals_core.h>

// Check for enabled C++17 support
#               if defined(_HAS_CXX17) && _HAS_CXX17
// We're using C++17, so let's use the normal version
#                   define INCLUDE_STD_FILESYSTEM_EXPERIMENTAL 0
#               endif
#           endif

// If the marco isn't defined yet, that means any of the other VS specific checks failed, so we need to use experimental
#           endif

// Not on Visual Studio. Let's use the normal version
#       else // #ifdef _MSC_VER
#       endif

// Check if the header "<filesystem>" exists
#   elif __has_include(<experimental/filesystem>)

// Fail if neither header is available with a nice error message
#   else
#       error Could not find system header "<filesystem>" or "<experimental/filesystem>"
#   endif

// We priously determined that we need the exprimental version
// Include it
#       include <experimental/filesystem>

// We need the alias from std::experimental::filesystem to std::filesystem
namespace std {
    namespace filesystem = experimental::filesystem;

// We have a decent compiler and can use the normal version
#   else
// Include it
#       include <filesystem>
#   endif


It even creates an alias for std::experimental::filesystem to std::filesystem if the experimental headers are used.
Which means you can simply include this header in place of <filesystem>, use std::filesystem::xxx and enjoy support from older compilers too.

A few notes on the details of this snippet:

  • __cpp_lib_filesystem and __cpp_lib_experimental_filesystem
    These are Feature Testing Macros. They should be available when the respecitive headers are available. But VisualStudio 2015 (and below) don't support them. So the rest is just to make sure we can make an accurate assesment, instead of relying on unreliable macros.
  • __has_include()
    While most compilers do have that macro built in, there is no gurantee, as it is not in the standard. My snippet checks for it's existence before it is used. And in case it doesn't exist, we assume we have to use the experimental version to provide maximum compatibility.
  • defined(_MSC_VER) && !(defined(_HAS_CXX17) && _HAS_CXX17)
    Some versions of VisualStudio (namely 2015) have just an half arsed implementation of C++17. And it's possible that the <filesystem> header exists, but std::filesystem doesn't. This line checks for that case and uses the experimental version instead.
  • #error ...
    If the header check is available and we can't find either header we just print a nice error, as there's nothing we can do.
    You even get a marco that let's you know which version is in usage so you could write pre processor statements of your own that deal with the differences between the versions.
  • namespace filesystem = experimental::filesystem;
    This alias definition is just for convinice that'll make sure that you'll have std::filesystem, assuming your compiler let's you do it (I haven't seen a single one that doesn't allow that).
    According to the standard defining anything in the std namespace is undefined behavior. So if your compiler, concience, colleguages, code standard or whatever complains, just define namespace fs = std::experimental::filesystem; in the upper block and namespace fs = std::filesystem; in the lower. (Just to be sure, if you do that, remove the namespace std { stuff)

P.S.: I created the answer and this question, because I spent an awful lot of time getting frustrated with older compilers not having the <filesystem> header. After a fair amount of research and testing on multiple platforms with multiple compilers and versions of them, I managed to come up with this universal solution. I have tested it with VisualStudio, g++ and clang (Only with versions that actually do have at least experimental support for C++17).
Should there be an issue with another compiler, let me know and I'll make it work for it too.

Saturday, August 20, 2022

Define performance. Your performance or the applications? Say you have the same rhtml snippet spread out across your views. Say you have it in thousands of places. Maybe you even haven't gotten it exactly the same in all places. Now your customer wants to change this (maybe different order of presentation or some such). It'll take you a while to do this in all the views, right? And chances are you won't get it right the first time. Chances are in fact that you'll keep getting bug reports for years to come on places you've missed to change.

The customer will end up paying a lot for that gained "performance". Maybe hundreds of working hours. Maybe tens of thousands if you avoid the DRY principle on principle. Think of all the servers and all the RAM she could buy for those work hours instead. If she spent it all on hardware her application might run hundred-folds faster. Think of all the fun things you could be working with instead of monkeying around changing html snippets.

Tuesday, October 11, 2022

I was just asking myself similar question and I think I found the "fresh" answer. Here is info from two weeks ago:

It seems that currently a long-inactive person is owner of the mysql-connector name, hence MySQL developers release under name mysql-connector-python.

Sunday, October 16, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :