高精度的MySQL NOW()函数 [英] MySQL NOW() function with high precision

问题描述

我正在研究一个C ++应用程序，该应用程序通过远程MySQL数据库存储其数据.

I am working on an C++ application that is storing its data through a remote MySQL database.

其中一项功能是尽可能保持远程数据的最新状态.为此，我使用NOW()函数获取上次更新时间，并且在更新记录时，将last_changed字段设置为NOW().

One of the features is to keep up to date with the remote data as much as possible. To achieve this, I use the NOW() function to get the last update time, and when updating records, set a last_changed field to NOW().

此功能目前可以正常工作，但问题是它的最大精度高达一秒.导致数十个重复条目，这浪费带宽，并且必须手动将其删除.

This works fine right now, apart form the issue that it has a maximum precision of up to one second. Causing dozens of duplicate entries, which wastes bandwidth, and having to manually remove them.

为了限制大多数膨胀，我希望有一个比这个更大的精度，最好是一个微秒(例如unix gettimeofday()).但是，我在文档中找不到与此有关的任何信息.我发现，NOW()函数使用的是变量类型，能够存储高达微秒的精度(

To limit most of this bloat, I would like a precision greater than this, preferably that up to microseconds (like unix gettimeofday()). However, I can not find any information about this on the documentation. What I can find is that the NOW() function is using a variable type, capable of storing up to microsecond precision (source). But when using it, all precision after the second are zeros.

是否有一种方法可以强制Mysql服务器使用更高精度的计时器(并认为可能的性能是理所当然的)?还是实现这一目标的另一种方法?

Is there a way to force the Mysql server to use a higher precision timer (and taking potential performance for granted)? Or another way to achieve this?

推荐答案

因此，在学习了技巧之后，研究了MySQL用户定义的函数，我能够使它起作用.

So, looking into MySQL user defined functions after a tip, I was able to get this to work.

在Code :: Blocks中，我编写了这个简单的库文件

In Code::Blocks I wrote this simple library file

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
typedef long long longlong;    
#include <mysql.h>
#include <ctype.h>    
#include <sys/time.h>
#include <unistd.h>
static pthread_mutex_t LOCK_hostname;
extern "C" longlong PreciseNow(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
extern "C" my_bool PreciseNow_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
extern "C" void PreciseNow_deinit(UDF_INIT *initid);
my_bool PreciseNow_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
 return 0; //optional
}
void PreciseNow_deinit(UDF_INIT *initid)
{ //optional
}

longlong PreciseNow(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
   struct timeval start;
   long mtime, seconds, useconds;
   gettimeofday(&start, NULL);
   return start.tv_sec * 1000000 + start.tv_usec;
}

它唯一要做的就是返回当前系统时间.我将lib文件放在'/usr/lib/mysql/plugin/'中. 其次，在数据库中，我执行了以下查询:

The only thing it does is return the current system time. I placed the lib file in '/usr/lib/mysql/plugin/'. Secondly, in the database, I excecuted the following query:

创建函数PreciseNow返回整数SONAME'libhrt.so'

CREATE FUNCTION PreciseNow RETURNS INTEGER SONAME 'libhrt.so'

最后，我将时间戳列转换为bigint(8字节整数).也许这不是必需的，但是看到C函数返回相同的对象类型，为什么不呢?

Finally I converted my timestamp columns to bigint (8 byte integers). Maybe this was not necessary but seeing the C function returns the same object type, why not...

现在，当运行SQL查询"SELECT PreciseNow()"时，我获得了内置的"NOW()"的精确到微秒的版本！ 由于我对MySQL的了解非常有限，因此我认为这是对我的问题的完全合法和有效的解决方案?

Now when running the SQL query "SELECT PreciseNow()" I get an microsecond precise version of the build-in "NOW()"! As I have very limited knowledge about MySQL, I assume this is a perfectly legal and valid solution to my problem?

这篇关于高精度的MySQL NOW()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！