delimiter // CREATE TRIGGER `asdf` BEFORE INSERT ON `test1` FOR EACH ROW begin declare inips bigint; select count(*) into inips from test where ((New.ip & mask) = (ip & mask)); if inips > 0 then set New.NewField = 'ok!'; end if; end; //delimiter ;这个是最后的。因为明白了一些事情!
这是测试数据:mysql> insert into test1 (ip) values(2043752001); Query OK, 1 row affected (0.00 sec)mysql> select * from test1 order by id desc limit 2; +----+------------+----------+ | id | ip | NewField | +----+------------+----------+ | 9 | 2043752001 | ok! | | 8 | 2043752021 | ok! | +----+------------+----------+ 2 rows in set (0.00 sec)mysql> insert into test1 (ip) values(2043052021); Query OK, 1 row affected (0.00 sec)mysql> select * from test1 order by id desc limit 2; +----+------------+----------+ | id | ip | NewField | +----+------------+----------+ | 10 | 2043052021 | NULL | | 9 | 2043752001 | ok! | +----+------------+----------+ 2 rows in set (0.00 sec)
vs8来编译libmysql这个你按照官方手册中的操作就可以了。看上去并不复杂,但没试过。 http://dev.mysql.com/doc/refman/5.1/en/connector-c-building.html Building on Microsoft Windows Older versions of Microsoft Windows are not supported. Supported versions are Windows 2000, Windows XP, Windows Vista, Windows Server 2003, or Windows Server 2008. Compiler Tools Microsoft Visual Studio 8 and 9 are recommended. The Express Edition of Visual Studio and other compilers may work, but are untested. You also need CMake 2.6 or newer, available at http://www.cmake.org To Build You need to have the environment variables set for the Visual Studio toolchain. Visual Studio includes a batch file to set these for you, and installs a shortcut into the Start menu to open a command prompt with these variables set. Build MySQL Connector/C using the CMake command-line tool by entering the following from the source root directory in a command prompt window: shell> cmake -G "Visual Studio 9 2008" This produces a project file that you can open with Visual Studio or build from the command line with either of: shell> devenv.com libmysql.sln /build Release shell> devenv.com libmysql.sln /build RelWithDebInfo For other versions of Visual Studio or nmake based build, run the following command: shell> cmake --help to check the supported generators. To compile the Debug build, you must run set the CMake build type so the correct version of external libraries are used: shell> cmake -G "Visual Studio 8 2005" -DCMAKE_BUILD_TYPE=Debug Followed by: shell> devenv.com libmysql.sln /build Debug To Install To create a install package you can choose between two variants: Creating a Zip package Creating an MSI install package Zip package To create a Zip package, run the cpack command from the root of your MySQL Connector/C source directory. MSI Install package The required tools include Windows XML Installer toolset (WIX), which is available online. To create the MSI install package change to the subdirectory win and generate the makefile: shell> cmake -G "NMake Makefiles" Create the MSI install package by calling nmake: shell> nmake
我怎么没有找到这个呢。。我只看了zip中带的installsourcewin.txt
这个在上面发的MYSQL官方文档链接中。
mysql> select SUBSTRING_INDEX('1.2.3.4','.',1) as a, substring_index(SUBSTRING_INDEX('1.2.3.4','.',2),'.',-1) as b, substring_index(SUBSTRING_INDEX('1.2.3.4','.',-2),'.',1) as c, substring_index('1.2.3.4','.',-1) as d; +---+---+---+---+ | a | b | c | d | +---+---+---+---+ | 1 | 2 | 3 | 4 | +---+---+---+---+ 1 row in set (0.00 sec) 这样感觉有点繁琐,有没有其他简练一点的方法呢?
-> `id` int(11) unsigned NOT NULL auto_increment,
-> `ip` bigint(20) default NULL,
-> `NewField` varchar(255) default NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.50 sec) mysql> delimiter //
mysql> CREATE TRIGGER `asdf` BEFORE INSERT ON `test1`
-> FOR EACH ROW
-> begin
-> declare lastid int;
-> declare insertip bigint;
-> declare inips bigint;//原来这里是int类型的。数据不能超过2147483647
-> select last_insert_id() into lastid;
-> select ip into insertip from test1 where id = lastid;
-> select count(*) into inips from test where ((ip & mask) = (insertip & mask));
-> if inips > 0 then
-> set New.NewField = 'ok!';
-> end if;
-> end;
-> //
Query OK, 0 rows affected (0.09 sec) mysql> delimiter ;
mysql> 触发起是触发了。但是因为数据类型问题,导致select count(*) into inips from test where ((ip & mask) = (insertip & mask));
这条语句为false。另外,你们有没有用vs8来编译libmysql?
没有编译过libmysql
第一步,到http://downloads.mysql.com/snapshots.php下载你自己想升级的版本。
第二步,将下载包解压到一个目录,用vc打开里面的mysql.dsw文件。选择只编译libmysql release。然后编译将得到一个libmysql.dll、libmysql.lib。
第三步,将libmysql.dll拷贝到win32build/lib/dll文件夹里面覆盖,将libmysql.lib拷贝到win32build/lib文件夹里面覆盖。
第四步,将mysql/include下面的my_alloc.h、mysql.h、mysql_com.h、mysql_version.h、 mysqld_error.h拷贝到win32build/include目录覆盖,将mysql/libmysql目录中的libmysql.def拷贝到win32build/include目录中。
第五步,重新编译php。安装php相关文件到相应目录中,将libmysql.dll拷贝到windows主目录(以2000中的winnt目录、2003的windows)目录中。
第六步,启动web服务器,看看phpinfo里面,是不是你的mysql库已经更新到最新版本?
这个时候应该还没有把数据插到表中的阿。因为这个问题,我才用after的
现在下载的包,主要是没有dsw工程文件。只有make文件。vc不能make。。据说用cmake可以弄,下载回来也不知道怎么弄了。网上没有详细说怎么用cmake。
有一个CMAKE的教程,下载看看
http://www.lazycodemonkey.com/?p=46=1
可以参考一下
但是
我这里的结果是可以的阿mysql> select * from test1 order by id desc limit 2;
+----+------------+----------+
| id | ip | NewField |
+----+------------+----------+
| 7 | 2043752021 | ok! |
| 6 | 2043752021 | NULL |
+----+------------+----------+
2 rows in set (0.00 sec)你看,第7条就是
哦,我知道了,用set就没有必要用到最后的lastinsertid了。明白鸟!
CREATE TRIGGER `asdf` BEFORE INSERT ON `test1`
FOR EACH ROW
begin
declare inips bigint;
select count(*) into inips from test where ((New.ip & mask) = (ip & mask));
if inips > 0 then
set New.NewField = 'ok!';
end if;
end;
//delimiter ;这个是最后的。因为明白了一些事情!
Query OK, 1 row affected (0.00 sec)mysql> select * from test1 order by id desc limit 2;
+----+------------+----------+
| id | ip | NewField |
+----+------------+----------+
| 9 | 2043752001 | ok! |
| 8 | 2043752021 | ok! |
+----+------------+----------+
2 rows in set (0.00 sec)mysql> insert into test1 (ip) values(2043052021);
Query OK, 1 row affected (0.00 sec)mysql> select * from test1 order by id desc limit 2;
+----+------------+----------+
| id | ip | NewField |
+----+------------+----------+
| 10 | 2043052021 | NULL |
| 9 | 2043752001 | ok! |
+----+------------+----------+
2 rows in set (0.00 sec)
http://dev.mysql.com/doc/refman/5.1/en/connector-c-building.html
Building on Microsoft Windows Older versions of Microsoft Windows are not supported. Supported versions are Windows 2000, Windows XP, Windows Vista, Windows Server 2003, or Windows Server 2008. Compiler Tools Microsoft Visual Studio 8 and 9 are recommended. The Express Edition of Visual Studio and other compilers may work, but are untested. You also need CMake 2.6 or newer, available at http://www.cmake.org To Build You need to have the environment variables set for the Visual Studio toolchain. Visual Studio includes a batch file to set these for you, and installs a shortcut into the Start menu to open a command prompt with these variables set. Build MySQL Connector/C using the CMake command-line tool by entering the following from the source root directory in a command prompt window: shell> cmake -G "Visual Studio 9 2008"
This produces a project file that you can open with Visual Studio or build from the command line with either of: shell> devenv.com libmysql.sln /build Release
shell> devenv.com libmysql.sln /build RelWithDebInfo
For other versions of Visual Studio or nmake based build, run the following command: shell> cmake --help
to check the supported generators. To compile the Debug build, you must run set the CMake build type so the correct version of external libraries are used: shell> cmake -G "Visual Studio 8 2005" -DCMAKE_BUILD_TYPE=Debug
Followed by: shell> devenv.com libmysql.sln /build Debug
To Install To create a install package you can choose between two variants: Creating a Zip package Creating an MSI install package Zip package To create a Zip package, run the cpack command from the root of your MySQL Connector/C source directory. MSI Install package The required tools include Windows XML Installer toolset (WIX), which is available online. To create the MSI install package change to the subdirectory win and generate the makefile: shell> cmake -G "NMake Makefiles"
Create the MSI install package by calling nmake: shell> nmake
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
+---+---+---+---+
1 row in set (0.00 sec)
这样感觉有点繁琐,有没有其他简练一点的方法呢?