create table a( id int auto_increment, name varchar(10), url varchar(20), PRIMARY KEY (id) )DELIMITER $$DROP TRIGGER `radius`.`mytri`$$create trigger `mytri` before insert on `a` for each row BEGIN select max(id)+1 into @id from a ; -- SET New.url = concat(LAST_INSERT_ID()+1,'.jpg'); Set New.url = concat(@id,'.jpg'); END; $$DELIMITER ;select mysql_insert_key() insert into a(name) values('name1')
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. 所以不能用triger里面的new.id 也不能用last_insert_id() 只能想到取此表中的最大id加1
这句运行出错: create trigger `mytri` before insert on `a` for each row BEGIN select max(id)+1 into @id from a ; Set New.url = concat(@id,'.jpg'); END; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(id)+1 into @id from a' at line 3
执行的时候执行 DELIMITER $$ create trigger `mytri` before insert on `a` for each row BEGIN select max(id)+1 into @id from a ; -- SET New.url = concat(LAST_INSERT_ID()+1,'.jpg'); Set New.url = concat(@id,'.jpg'); END; $$
我这边出错SQL 查询: DELIMITER $$ CREATE trigger `mytri` before INSERT ON `a` FOR each ROW BEGIN SELECT max( id ) +1 INTO @id FROM a; MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ create trigger `mytri` before insert on `a` for each row BEGIN ' at line 1 我的建表语句是: CREATE TABLE `a` ( `id` int(11) NOT NULL auto_increment, `name` varchar(10) collate gb2312_bin default NULL, `url` varchar(20) collate gb2312_bin default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin AUTO_INCREMENT=3 ;我用的是phpmyadmin
自己找找相关的帮助吧,建立触发器,实际内容就是两行 select max(id)+1 into @id from a ; Set New.url = concat(@id,'.jpg');
id int auto_increment,
name varchar(10),
url varchar(20),
PRIMARY KEY (id)
)DELIMITER $$DROP TRIGGER `radius`.`mytri`$$create trigger `mytri` before insert on `a`
for each row BEGIN
select max(id)+1 into @id from a ;
-- SET New.url = concat(LAST_INSERT_ID()+1,'.jpg');
Set New.url = concat(@id,'.jpg');
END;
$$DELIMITER ;select mysql_insert_key()
insert into a(name) values('name1')
所以不能用triger里面的new.id 也不能用last_insert_id() 只能想到取此表中的最大id加1
create trigger `mytri` before insert on `a`
for each row BEGIN
select max(id)+1 into @id from a ;
Set New.url = concat(@id,'.jpg');
END; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select max(id)+1 into @id from a' at line 3
DELIMITER $$
create trigger `mytri` before insert on `a`
for each row BEGIN
select max(id)+1 into @id from a ;
-- SET New.url = concat(LAST_INSERT_ID()+1,'.jpg');
Set New.url = concat(@id,'.jpg');
END;
$$
DELIMITER $$ CREATE trigger `mytri` before INSERT ON `a`
FOR each
ROW BEGIN
SELECT max( id ) +1
INTO @id
FROM a;
MySQL 返回:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
create trigger `mytri` before insert on `a`
for each row BEGIN
' at line 1 我的建表语句是:
CREATE TABLE `a` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(10) collate gb2312_bin default NULL,
`url` varchar(20) collate gb2312_bin default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin AUTO_INCREMENT=3 ;我用的是phpmyadmin
select max(id)+1 into @id from a ;
Set New.url = concat(@id,'.jpg');