sql - partir - que es un campo autoincremental

Campos de AutoIncrement en bases de datos sin campo de autoincrement

Question

En MS Sql Server es fácil crear campos de autoincrement. En mis sistemas me detuve para usar campos de autoincrement para claves primarias, y ahora uso Guid''s. Fue increíble, tengo muchas ventajas con ese cambio. Pero en otros campos clave no primarios, realmente necesitaba implementar una "autoincrementación suave". Es porque mi sistema es independiente de DB, así que creo el valor autoinc programáticamente en c #.

Me gustaría obtener soluciones para campos de autoincrement en bases de datos sin autoincrement, ¿cuál es la solución que su uso y por qué? Hay alguna declaración Sql Ansi sobre esto? y generar directamente desde mi c #, ¿es una mejor solución?

PD: Sé que seleccionar max (id) +1 de la tabla no es realmente amigable concurrente ...

Answer 1

La mayoría de las bases de datos que no tienen campos de autoincrementación como SQL Server (estoy pensando específicamente en Oracle) tienen secuencias en las que pregunta a la secuencia por el siguiente número. No importa cuántas personas soliciten números al mismo tiempo, todos obtienen un número único.

Answer 2

La solución tradicional es tener una tabla de identificadores que se parecen a esto

CREATE TABLE ids (
  tablename VARCHAR(32) NOT NULL PRIMARY KEY,
  nextid INTEGER
)

que se llena con una fila por tabla cuando crea la base de datos.

A continuación, haga una selección para obtener la próxima identificación siguiente para la tabla en la que está insertando, increméntela y luego actualice la tabla con la nueva identificación. Obviamente, hay problemas de bloqueo aquí, pero para las bases de datos con tasas de inserción moderadas funciona bien. Y es completamente portátil.

Answer 3

Creo que tu pregunta es bastante buena. Sin embargo, es fácil perderse tratando de encontrar una solución solo de SQL. En realidad, querrá la optimización y la seguridad de las transacciones que ofrece el uso de las implementaciones de base de datos de los tipos de autoincremento.

Si necesita abstraer la implementación del operador de autoincrement, ¿por qué no crear un procedimiento almacenado para devolver su valor de autoincrement? La mayoría de los dialectos SQL acceden a los procedimientos almacenados de forma relativamente similar y deberían ser más portátiles. Luego puede crear una lógica de autoincrementación específica de la base de datos cuando crea el sproc, eliminando la necesidad de cambiar muchas declaraciones para que sean específicas del proveedor.

Hecho de esta manera, sus insertos podrían ser tan simples como:

INSERT INTO foo (id, name, rank, serial_number)
 VALUES (getNextFooId(), ''bar'', ''fooRank'', 123456);

Luego defina getNextFooId () de una manera específica de la base de datos cuando la base de datos se está inicializando.

Answer 4

Si necesita un campo de autoincrementación de clave no primaria, una solución muy buena de MySQL para crear secuencias arbitraty es usar la función last_insert_id(expr) relativamente desconocida.

Si expr se da como argumento para LAST_INSERT_ID (), la función devuelve el valor del argumento y se recuerda como el siguiente valor que devuelve LAST_INSERT_ID (). Esto se puede usar para simular secuencias ...

(de http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id )

Aquí hay un ejemplo que demuestra cómo se puede mantener una secuencia secundaria para numerar los comentarios de cada publicación:

CREATE TABLE  `post` (
  `id` INT(10) UNSIGNED NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `comment_sequence` INT(10) UNSIGNED NOT NULL DEFAULT ''0'',
  PRIMARY KEY (`id`)
);

CREATE TABLE  `comment` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`  INT(10) UNSIGNED NOT NULL,
  `sequence` INT(10) UNSIGNED NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO post(id, title) VALUES(1, ''first post'');
INSERT INTO post(id, title) VALUES(2, ''second post'');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), ''blah'');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), ''foo'');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), ''bar'');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), ''lorem'');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), ''ipsum'');

SELECT * FROM post;
SELECT * FROM comment;

Answer 5

El mecanismo para generar valores únicos de identificación no debe estar sujeto al aislamiento de transacciones. Esto es necesario para que la base de datos genere un valor distinto para cada cliente, mejor que el truco de SELECT MAX(id)+1 FROM table , que da como resultado una condición de carrera si dos clientes intentan asignar nuevos valores de id mismo tiempo.

No puede simular esta operación utilizando consultas SQL estándar (a menos que use bloqueos de tabla o transacciones serializables ). Tiene que ser un mecanismo integrado en el motor de la base de datos.

ANSI SQL no describió una operación para generar valores únicos para claves sustitutas hasta SQL: 2003. Antes de eso, no había un estándar para las columnas de incremento automático, por lo que casi todas las marcas de RDBMS proporcionaban alguna solución propietaria. Naturalmente, varían mucho y no hay forma de usarlos de una manera simple, independiente de la base de datos.

• MySQL tiene la opción de columna AUTO_INCREMENT o SERIAL pseudo-datatype que es equivalente a BIGINT UNSIGNED AUTO_INCREMENT ;
• Microsoft SQL Server tiene la opción de columna IDENTITY y NEWSEQUENTIALID() que es algo entre NEWSEQUENTIALID() y GUID;
• Oracle tiene un objeto SEQUENCE ;
• PostgreSQL tiene un objeto SEQUENCE , o pseudo-tipo de datos SERIAL que implícitamente crea un objeto de secuencia de acuerdo con una convención de nomenclatura;
• InterBase / Firebird tiene un objeto GENERATOR que se parece mucho a una SEQUENCE en Oracle; Firebird 2.1 admite SEQUENCE también;
• SQLite trata cualquier entero declarado como clave principal como autoincrementado de forma implícita;
• DB2 UDB tiene casi todo: objetos SEQUENCE , o puede declarar columnas con la opción " GEN_ID ".

Todos estos mecanismos operan fuera del aislamiento de transacciones, asegurando que los clientes concurrentes obtengan valores únicos. También en todos los casos hay una forma de consultar el valor generado más recientemente para su sesión actual . Tiene que haberlo, así que puede usarlo para insertar filas en una tabla secundaria.