tablas - ¿Puedes acceder al valor de incremento automático en MySQL dentro de una declaración?

view lenta mysql

Question

Tengo una base de datos MySQL que contiene una tabla de usuarios. La clave principal de la tabla es ''userid'', que se configura como un campo de incremento automático.

Lo que me gustaría hacer es insertar un nuevo usuario en la tabla es usar el mismo valor que el incremento automático está creando en el campo ''userid'' en un campo diferente, ''default_assignment''.

p.ej

Me gustaría una declaración como esta:

INSERT INTO users (''username'',''default_assignment'') VALUES (''barry'', value_of_auto_increment_field())

entonces creo el usuario ''Barry'', el ''userid'' se genera como 16 (por ejemplo), pero también quiero que ''default_assignment'' tenga el mismo valor de 16.

¿Hay alguna manera de lograr esto, por favor?

¡Gracias!

Actualizar:

Gracias por las respuestas El campo default_assignment no es redundante. La Default_assigment puede hacer referencia a cualquier usuario dentro de la tabla de usuarios. Al crear un usuario, ya tengo un formulario que permite la selección de otro usuario como la asignación predeterminada, sin embargo, hay casos en los que debe establecerse para el mismo usuario, de ahí mi pregunta.

Actualizar:

Ok, probé la sugerencia de desencadenantes de actualización pero todavía no puedo hacer que esto funcione. Aquí está el disparador que he creado:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users`  
FOR EACH ROW BEGIN
SET NEW.default_assignment = NEW.userid;
END;

Al insertar un nuevo usuario, sin embargo, la asignación_de_inicio predeterminada siempre se establece en 0.

Si configuro manualmente el ID de usuario, la asignación_default se establece en el ID de usuario.

Por lo tanto, el proceso de generación automática de asignaciones ocurre claramente después de que el desencadenante entre en vigor.

Answer 1

Básicamente, la solución es como dijo Resegue .
Pero si lo quiere en una declaración, usará una de las siguientes formas:

1. Una declaración larga:

INSERT INTO `t_name`(field) VALUES((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''t_name''))

o para texto con número:

INSERT INTO `t_name`(field) VALUES(CONCAT(''Item No. '',CONVERT((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''t_name'') USING utf8)))

se ve más claramente en PHP:

$pre_name=''Item No. '';
$auto_inc_id_qry = "(SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''$table'')";
$new_name_qry = "CONCAT(''$pre_name'',CONVERT($auto_inc_id_qry USING utf8))";
mysql_query("INSERT INTO `$table`(title) VALUES($new_name_qry)");

2. Uso de la función: (aún no probado)

CREATE FUNCTION next_auto_inc(table TINYTEXT) RETURNS INT
BEGIN
DECLARE next_id INT;
SELECT AUTO_INCREMENT FROM information_schema.TABLES
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=table INTO next_id;
RETURN next_id;
END

INSERT INTO users (''username'',''default_assignment'')
    VALUES (''barry'', next_auto_inc(''users''))

Answer 2

En realidad, intenté hacer lo mismo que sugerí anteriormente. Pero parece que Mysql no genera el ID insertado antes de que la fila realmente se haya comprometido. Entonces NEW.userid siempre devolverá 0 en un disparador Before insert.

Lo anterior tampoco funcionará a menos que sea un desencadenante ANTES DE INSERTAR, ya que no puede actualizar valores en una consulta DESPUÉS DE INSERTAR.

Desde una publicación en el foro de Mysql Parece que la única forma de manejar esto es usar una tabla adicional como secuencia. De modo que su activador pueda obtener los valores de una fuente externa.

CREATE TABLE `lritstsequence` (
  `idsequence` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;

CREATE TABLE `lritst` (
`id` int(10) unsigned NOT NULL auto_increment,
`bp_nr` decimal(10,0) default ''0'',
`descr` varchar(128) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `dir1` (`bp_nr`)
) ENGINE=InnoDB;

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$$

CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst`
FOR EACH ROW
BEGIN
    DECLARE secuencia INT;
    INSERT INTO lritstsequence (idsequence) VALUES (NULL);
    SET secuencia = LAST_INSERT_ID();
    SET NEW.id = secuencia;
    SET NEW.bp_nr = secuencia;
END;$$

DELIMITER ;

INSERT INTO lritst (descr) VALUES (''test1'');
INSERT INTO lritst (descr) VALUES (''test2'');
INSERT INTO lritst (descr) VALUES (''test3'');

SELECT * FROM lritst;

Result:

    id   bp_nr  descr 
------  ------  ------
     1       1  test1 
     2       2  test2 
     3       3  test3

Esto fue copiado de forums.mysql.com/read.php?99,186171,186241#msg-186241 pero todavía no puedo publicar enlaces.

Answer 3

Lo único que encontré que resolvería este problema sin una tabla adicional sería calcular el número siguiente y ponerlo en los campos requeridos.

CREATE TABLE `Temp` (
  `id` int(11) NOT NULL auto_increment,
  `value` varchar(255) ,
  PRIMARY KEY  (`idsequence`)
) ENGINE=InnoDB;


CREATE TRIGGER temp_before_insert BEFORE INSERT ON `Temp`
FOR EACH ROW 
BEGIN
    DECLARE m INT;

    SELECT IFNULL(MAX(id), 0) + 1 INTO m FROM Temp;
    SET NEW.value = m;
    -- NOT NEEDED but to be save that no other record can be inserted in the meanwhile
    SET NEW.id = m;   
END;

Answer 4

Probé la idea del disparador anterior con 10 hilos simultáneos haciendo inserciones y obtuve más de 1000 casos de 2 o 3 duplicados después de insertar ~ 25k.

DROP TABLE IF EXISTS test_table CASCADE;

CREATE TABLE `test_table` (
  `id`             INT         NOT NULL  AUTO_INCREMENT,
  `update_me`      VARCHAR(36),
  `otherdata`      VARCHAR(36) NOT NULL,

  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COMMENT ''test table for trigger testing'';

delimiter $$

DROP TRIGGER IF EXISTS setnum_test_table;
$$
CREATE TRIGGER setnum_test_table
BEFORE INSERT ON test_table FOR EACH ROW
-- SET OLD.update_me = CONCAT(NEW.id, ''xyz'');

BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''test_table'' LOCK IN SHARE MODE );
--    SET NEW.update_me = CONCAT(next_id, ''qrst'');
    SET NEW.update_me = next_id;
END

$$

delimiter ;

-- SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''test_table''

INSERT INTO test_table (otherdata) VALUES (''hi mom2'');

SELECT count(*) FROM test_table;
SELECT * FROM test_table;

-- select count(*) from (
select * from (
SELECT count(*) as cnt ,update_me FROM test_table group by update_me) q1
where cnt > 1
order by cnt desc

Usé 10 de:

while true ; do echo "INSERT INTO test_table (otherdata) VALUES (''hi mom2'');" | mysql --user xyz testdb ; done &

Y ejecutó la última consulta para ver duplicados

Ejemplo de salida: ''3'', ''4217'' ''3'', ''13491'' ''2'', ''10037'' ''2'', ''14658'' ''2'', ''5080'' ''2'', ''14201'' ...

Note que ''BLOQUEO EN COMPARTIR MODO'' no cambió nada. Con y sin dio duplicados a aproximadamente la misma velocidad. Parece que MySQL AUTO_INCREMENT no funciona como Postgres ''next_val () y NO es concurrente seguro.

Answer 5

Puede hacerlo de manera confiable usando una subconsulta simple:

INSERT INTO users (''username'',''default_assignment'')
    SELECT ''barry'', Auto_increment FROM information_schema.tables WHERE TABLE_NAME=''users''

Answer 6

Sé que esta publicación es de 2010, pero no pude encontrar una buena solución. He resuelto esto creando una tabla separada que contiene los contadores. Cuando necesito generar un identificador único para una columna, simplemente llamo a un proc. Almacenado:

CREATE DEFINER=`root`@`localhost` PROCEDURE `IncrementCounter`(in id varchar(255))
BEGIN
declare x int;
-- begin;
start transaction;
-- Get the last counter (=teller) and mark record for update.
select Counter+1 from tabel.Counter where CounterId=id into x for update;
-- check if given counter exists and increment value, otherwise create it.
if x is null then
    set x = 1;
    insert into tabel.Counters(CounterId, Counter) values(id, x);
else
    update tabel.Counters set Counter = x where CounterId = id;
end if;
-- select the new value and commit the transaction
select x;
commit;
END

La instrucción ''para actualizar'' bloquea la fila en la tabla de contadores. Esto evita que los duplicados sean hechos por múltiples hilos.

Answer 7

no es necesario crear otra tabla, y max () tendrá problemas de acuerdo con el valor auto_increment de la tabla, haga esto:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id INT;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=''tbl'');
   SET NEW.field=next_id;
END

Declaro la variable next_id porque generalmente se usará de alguna otra forma (*), pero podrías hacer new.field = (seleccionar ...)

(*) To auto-name an image:
SET NEW.field = CONCAT(''image_'', next_id, ''.gif'');
(*) To create a hash:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );

Answer 8

prueba esto

INSERT INTO users (default_assignment) VALUES (LAST_INSERT_ID()+1);

Answer 9

viendo que last_insert_id() no funcionaría en este caso, sí, el disparador sería la única manera de lograr eso.

Sin embargo, me pregunto: ¿para qué necesitas esta funcionalidad? ¿Por qué almacena la identificación de los usuarios dos veces? Personalmente, no me gusta almacenar datos redundantes como este y probablemente resolvería esto en el código de la aplicación haciendo que la ominosa columna default_assignment NULL y el uso de la identificación del usuario en el código de mi aplicación, si default_assignment fuera NULL.

Answer 10

$ret = $mysqli->query("SELECT Auto_increment FROM information_schema.tables WHERE table_schema = DATABASE() ");l
while ($row = mysqli_fetch_array($ret)) {
    $user_id=$row[''Auto_increment''];
}