Parametrizar consulta en MySQL usando @variables

Posted on by

Introducción

Hace poco en un proyecto personal he empezado a usar variables en mis consultas SQL para probarlas en mi cliente HeidiSQL. De esto modo puedo verificar el funcionamiento de las mismas como las construye mi framewoork codeigniter.

Por poner un ejemplo, en PHP plano se escribiría algo así:

$select = 'select * from cosas where precio > ', ($miprecio + $parametro) ' and precio < ', ($miprecio * 10) ;.

Pero claro, lo ideal es poder usar en MySQL una construcción con el mismo aspecto, ¿cómo? Usando las variables que nos ofrece en SQL.

Ejemplos

Supongamos una tabla de viviendas con su id, nombre y precio de mercado como la siguiente:

select id, nombre, precio_de_venta from kaza_viviendas
id nombre precio_de_venta
12 Villa Atlas 1470000
10 Villa Chronos 1350000
11 Villa Hermes 780000
6 Villa Marfil 1300000
8 Villa Eos 850000
9 Villa Pegasus 670000

Si queremos el precio más bonito y usando otro nombre:

select id, nombre, format(precio_de_venta, 2) as precio from viviendas
id nombre precio
12 Villa Atlas 1,470,000.00
10 Villa Chronos 1,350,000.00
11 Villa Hermes 780,000.00
6 Villa Marfil 1,300,000.00
8 Villa Eos 850,000.00
9 Villa Pegasus 670,000.00

A continuación, vamos a crear dos parámetros “horquilla” y “precio_referencia”. El primero establece un límite sobre el que buscar precio “similares” al dado por “precio_referencia”:

set @precio_referencia=1470000;
set @horquilla=25000;
select id, nombre, format(precio_de_venta, 2) as recio
from viviendas
where precio_de_venta >= (@precio_referencia - @horquilla)
  and precio_de_venta <= (@precio_referencia + @horquilla);

Como vemos sólo nos devuelve una. Pero para generar y probar una lista de viviendas similares con este u otro criterio resulta muy útil y rápido

id nombre recio
12 Villa Atlas 1,470,000.00

Estos ejemplos son muy sencillo y tontos, pero creo que ilustran a grandes rasgos lo que quiero decir, además estoy seguro que para construir consultas más complejos con mayor cantidad de cruzas y condiciones podría ser de gran ayuda.

Conclusión

En desarrollo personalmente no me gusta usar variables de MySQL, aunque con ejemplos como el anterior si que veo muy claro lo que facilita la depuración del SQL y ayuda a la hora de construir consultas muy complejas con muchos datos cruzas y poder probar diferentes casos.

34 thoughts on “Parametrizar consulta en MySQL usando @variables”

  1. Pingback: Parametrizar consulta en MySQL usando @variables » DbRunas

  2. Alguien me puede ayudar con est:

    declare @column nvarchar(7);
    declare @IdConc int;
    declare @Contador int;
    set @Contador = 0;
    while @Contador < 2
    begin
    set @column = 'IdConc'+cast(@Contador as varchar);
    set @IdConc = (select @column from DatosProyec);
    set @Contador = @Contador + 1;

    print 'valor :' +cast(@IdConc AS varchar);

    end

    Mi problema es que quiero moverme a través de las columnas
    de mi tabla para obtener los registros de estas pero me marca error de
    compatibilidad de variables y es que el que quiero obtener es un numérico no
    texto

    Reply ↓

  3. Tengo este código realizado para una vista en HeidiSql que poniendo un valor me funciona pero no consigo que me funcione poniendo un parámetro. Declaro una variable y luego pongo el igual a @variable pero da error.

    SELECT clientes.dni,clientes.nombre,provincias.provincia,Clientes.cp,municipios.municipio,clientes.calle,clientes.numero,gestion.fecha,gestion.fecha1,gestion.juntas,gestion.texto
    From clientes
    INNER JOIN GESTION ON clientes.dni = gestion.dni
    INNER JOIN MUNICIPIOS ON clientes.cp = municipios.cp
    INNER JOIN PROVINCIAS ON clientes.idprov = provincias.idprov
    Where clientes.dni= ‘15681942 R’

    Reply ↓

  4. Hola Jose
    Tengo un programa en Visual Studio con este código.
    Open()
    Dim Sql As String
    Sql = “Select ” & campo & ” From ” & miTabla & ” Where ” & clave & ” like ‘” & Texto & “%'”
    Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(Sql, cn)
    Return CStr(cmd.ExecuteScalar)

    Pero lo que ahora quiero es hacer una consulta en MariaDB y pasarle los parámetro en la llamada desde del programa.
    Pregunto si es posible hacerlo ya que no he visto documentación sobre esta forma de trabajar.

    Reply ↓

  5. hola me podrian ayudar quiero sacar el total de horas de una tabla la entrada y salida estan en una misma columna pero quiero sacar las horas extras despues de las 5 las horas laboradas despues de los sabados a medio dia y las horas laboradas si es un domingo se puede?

    Reply ↓

    • Buenos días, poder se debe poder. Ten en cuenta que A) No sabemos que DBMS estás usando y B) no sabemos la estructura de la tabla. Para solucionarlo debes partir el problema en subproblemas:
      – Dada una fecha (conocer el día de la semana, para discrimar entre semnaa, sábado y domingo)
      – Suponiendo que tienes un flag que marca entrada/salida diferenciar con este flag para hacer el computo entre entradas y salidas
      – Unir todas las condiciones para obtener la suma

      Reply ↓

  6. Buenas tardes, no se si me podrás ayudar con esta duda que tengo. Si ejecuto esta sentencia SQL desde phpmyadmin me devuelve los resultados correctamente:
    “set @total:=0;
    select id_pm_s, fecha, tipo, cantidad, destino, observaciones, @total:=cantidad+@total as Saldo from
    libros_pm_salidas;”
    (es para calcular una columna “saldo” automáticamente)
    Mi pregunta es como puede pasar esta consuta sql en el códiog php? He probado de varias formas y me da errores.
    Ejemplo: $query = sprintf(“set @total:=0; select id_pm_s, fecha, tipo, cantidad, destino, observaciones, @total:=cantidad+@total as Saldo from libros_pm_salidas; WHERE Fecha BETWEEN ‘$year/$min’ AND ‘$year/$max’ ORDER BY fecha Asc”);

    ¿Como se incluye una variable auxiliar de sql en una consulta php?

    Reply ↓

    • Hola, te voy a contestar “de memoria”. En PhpMyAdmin puedes ejecutar un lote de sentencias SQL sin problema. Pero en PHP si estás usando la extensión mysqli el método query (http://php.net/manual/es/mysqli.query.php) tienes que ejecutar sentencia a sentencia. Es decir: 1) declara la variables y 2) usala en tu SELECT. No te preocupes, al estar dentro de la misma sesión de base de datos no se borra. Sería algo así:


      $mysqli = new mysqli("localhost", "my_user", "my_password", "world");
      if ($mysqli->connect_errno) {
      exit();
      }

      $mysqli->query("set @total:=0");
      $resultado = $mysqli->query(sprintf(“select id_pm_s, fecha, tipo, cantidad, destino, observaciones, @total:=cantidad+@total as Saldo from libros_pm_salidas WHERE Fecha BETWEEN ‘$year/$min’ AND ‘$year/$max’ ORDER BY fecha Asc”);
      ....

      Ya ajustas tus parámetros de conexión y la consulta SQL de la SELECT.

      Saludos

      Reply ↓

  7. Hola… Tengo que hacer una consulta a MySQL donde debo tener una columna que muestra un número único por cada fila. Lo que en la versión 8 de MySQL hace row_number(), en la ver 5.7 hay que emularlo ya que dicha función no existe.-

    My consulta SQL y comandos son así:

    qry = “SELECT @rownum1:=@rownum1+1 AS rownum, fld1, fld1 FROM ( SELECT @rownum1:=0 ) r, tablename WHERE fld2=’some_value’ ORDER BY fld2”
    Dim cmd As New MySqlCommand(qry, conbd)
    cmd.Parameters.AddWithValue(“@rownum1”, 0)
    Dim dr As MySqlDataReader = cmd.ExecuteReader

    Con @rownum1 creo el número único por cada fila (con ayuda de http://www.mysqltutorial.org/mysql-row_number/). El tema es que cuando ejecuto desde dentro de VB.NET la consulta falla porque me traduce el parámetro @rownum1 al valor 0 enviado con el comando …

    cmd.Parameters.AddWithValue(“@rownum1”, 0)

    Pero si no lo envío, me da error porque dice que debo definir dicho parámetro.

    El error me lo marca en:
    near ‘:=0+1 AS rownum

    Muchas gracias!

    Reply ↓

    • Hola, pues hace mucho mucho que no toco Vb.NET pero por el ejemplo que pones creo que no es un problema de variables en servidor. Parece que el driver de BBDD (imagino que ADO.NET) coge tu “variable” y la usa como si estuvieras haciendo una consulta preparada que entiendo que no es lo que quieres.

      Reply ↓

  8. COMO LE PUEDO SACAR PARAMETROS A ESTA VISTA?
    CREATE VIEW Promedios AS
    SELECT * FROM tbproduccion
    SELECT
    *
    FROM tbproduccion
    WHERE tbproduccion.Pozo_ID = ID
    and (tbproduccion.Fecha >= CONCAT(FECHA, “-01-01”)
    AND tbproduccion.Fecha <= CONCAT(FECHA, "-12-31"));

    Reply ↓

    • Hola, una vista “debe” encapsular una consulta dada. Es decir, una consulta más “compleja” con sus restricciones y condiciones. Si por algún motivo alguna de esas restricciones es necesario parametrizarla fuera de la vista lo que debes hacer es definir la vista sin esa restricción y luego añadir el WHERE sobre la vista con tu parámetro en tu consulta preparada.

      Ejemplo:

      Definir la vista

      CREATE VIEW EmpleadosActivos AS
      SELECT e.nombrecompleto, e.edad, e.salario, d.nombre, d.codigo
      FROM empleados e
      INNER JOIN departamentos d ON e.departamento_id = d.id_empleado
      WHERE e.activo IS TRUE;

      Empleados que ganen más de 30,000 EUR en informática (consulta preparada):

      SELECT * FROM EmpleadosActivos WHERE salario > ? AND codigo = ?:

      Usando una única consulta:

      SET @salario=30000;
      SET @codigo='INF';
      SELECT * FROM EmpleadosActivos WHERE salario > @salario AND codigo = @codigo;

      Saludos!

      Reply ↓

      • SI ENTENDI LO QUE ME DIJISTE PERO ME AYUDARIAS A RESOLVERLO O ME LO PODRIAS RESOLVER PORFA NECESITO SACARLE PARAMETROS A ESA VISTA PARA PODER OBTENER EL PROMEDIO POR UN MES, YA ANTERIORMENTE LO SAQUE POR UN AÑO, PERO AHORA NECESITO EL PROMEDIO POR UN MES DE ANTE MANO GRACIAS..
        COMO LE PUEDO SACAR PARAMETROS A ESTA VISTA?
        CREATE VIEW Promedios AS
        SELECT * FROM tbproduccion
        SELECT
        *
        FROM tbproduccion
        WHERE tbproduccion.Pozo_ID = ID
        and (tbproduccion.Fecha >= CONCAT(FECHA, “-01-01”)
        AND tbproduccion.Fecha <= CONCAT(FECHA, "-12-31"));

        Reply ↓

        • Hola:
          1- Por favor no ESCRIBAS MAYÚSCULAS, en Internet eso significa chillar
          2- Las vistas NO se pueden parametrizar, encapsulan una consulta SQL más compleja
          3- Lo que quieres hacer es declarar tu vista, exponiendo las columnas por las que quieres filtrar y hacer un Prepared Stament (consulta preparada) y filtrar sobre la vista: SELECT * FROM vista WHERE campoVISTA = ….”
          4- Hacertelo, no te lo voy a hacer, flaco favor sería.

          Espero que los comentarios te ayuden.

          PS: Igual te interesa mirar procedimientos almacenados

          Reply ↓

  9. Buenos dias estoy realizando una consulta para despues hacer un update
    la consulta es que si la hora final este vacia me la rellene con la ahora actual
    y me actualice los camos de observacion y hora final
    me da error
    Recoverable fatal error: Object of class mysqli_result could not be converted to string in

    <?php

    $sql="SELECT id FROM registro WHERE pin=".$pin." and finalizar='0000-00-00 00:00:00'";
    // Comprobamos que se ejecuta la consulta
    $var= mysqli_query($conn,$sql);

    $sql="UPDATE registro SET observacion =".$observacion.",finalizar=NOW() WHERE id=".$var;

    if (mysqli_query($conn, $sql)) {
    echo " alert(‘tarea finalizada Correctamente’);”;

    }
    mysqli_close($conn);

    ?>

    Reply ↓

    • Hola, veo varías cosas que me llaman la atención. Por un lado la consulta:

      SELECT id FROM registro WHERE pin=$pin and finalizar='0000-00-00 00:00:00 LIMIT 1

      Devuelve de 0 a N registros con lo que en $var nunca tendra un único resultado sino un array.
      Por otro lado, puedes hacerlo todo de golpe con SQL con la consulta:

      UPDATE registro SET observacion =$observacion.,finalizar=NOW()
      WHERE id IN (
      SELECT id FROM registro WHERE pin=$pin and finalizar='0000-00-00 00:00:00
      )

      Es una idea. Suerte!

      Reply ↓

  10. Hola, de antemano saludos, felicitaciones por tu página y mi agradecimiento por tu atención. Quisiera pedir tu ayuda con lo siguiente:
    Quiero actualizar toda la columna “cantidad final” de la tabla “kardex”. Que me recorra todos los registros y evalúe por el producto en cada fila (producto_id) y realice la suma de las cantidades cuanto los tipos de comprobante sean 2 y 3 y reste la sumatoria de las cantidades cuando tipo de comprobante sea 1 y 4, esta operación irá en la columna “cantidad_final”.

    Todo esto en MariaDB PhpMyAdmin

    Mi inconveniente es que he probado la siguiente sentencia (o algoritmo si así puede llamarse) pero no he obtenido el resultado esperado, por lo que pido tu atencción si puedes revisar el código:

    SELECT id,fecha,tipo_comprobante,producto_id,detalle_id,cantidad,valor_unitario,importe,cantidad_final FROM kardex;
    /*Luego, recorriendo la consulta en un bucle, en cada iteracion hacer*/
    SET @variable_cantidad_final = 0;
    SET @variable_id = id;
    WHILE (@variable_id > 0)
    @variable_id = id
    @variable_cantidad = cantidad
    @variable_producto_id = producto_id
    @variable_cantidad_final += select sum(cantidad=@variable_cantidad) from kardex where producto_id=@variable_producto_id and (tipo_comprobante=2 and 3) – (select sum(cantidad=@variable_cantidad) FROM kardex where producto_id = @variable_producto_id and (tipo_comprobante=1 and 4));
    UPDATE kardex
    SET cantidad_final = @variable_cantidad_final
    WHERE id = @variable_id

    Reply ↓

    • Hola!!

      Yo creo que esto con un UPDATE y un SELECT dentro igual lo puedes hacer.

      UPDATE kardex
      SET cantidad_final = (
      select sum(cantidad) from kardex where producto_id=:variable_id and (tipo_comprobante=2 and 3) – (select sum(cantidad) FROM kardex where producto_id = :variable_id and (tipo_comprobante=1 and 4))
      )
      WHERE id = :variable_id

      Ánimo!!

      Reply ↓

  11. Hola. Gracias por permitir hacer una consulta. Necesito tomar los datos de una base y enviar un correo, segun la consulta que realizo. El problema reside que esta todo, supuestamente, en orden, pero no me enviar el correo de acuerdo al contenido de la variable.Desde ya Muchas Gracias.
    El php es este, que es continuación de un formulario:
    query($sql);
    $link = require ‘conexion.php’;
    $result = mysqli_query($link, “SELECT * FROM imeil WHERE apodo = ‘$para’ “);
    mysqli_data_seek ($result, 0);
    if ($extraido= mysqli_fetch_array($result)){
    echo “- De: “.$de.””;
    echo “- Nombre: “.$extraido[‘apellido’].””;
    echo “- Clave: “.$extraido[‘oficina’].””;
    echo “- CORREO: “.$extraido[‘mail’].””;
    echo “_ TEMA: “.$tema.””;
    echo “_ MENSAJE:”.$resumen.””;
    } else {
    echo ” No se ha encontrado ningun registro.Intentelo de Nuevo. “;
    }
    mysqli_free_result($result);
    $cuerpo=”De: “.$de.”\r\n”.”Tema: “.$tema.”\r\n”.”Mensaje: “.$resumen ;
    @mail($mail, $tema, $cuerpo) ;
    header(“Location: ingrecom.php?env=Ok#registrese”);
    ?>

    Reply ↓

    • Hola, de fondo veo varios problemas.

      1 – Te recomiendo que pasas de usar mysqli a PDO, es más sencillo y cómodo.
      2 – La línea : @mail($mail, $tema, $cuerpo) ; — $mail, $tema y $cuerpo no aparecen definidos. Veo echos arriba, pero no asignas valores … ¿puede que lleguen vacios los valores a mail() ?
      3 – No uses la función mail() del sistema, usa una libreria tipo PHPMailer o swiftmailer.

      Saludos

      Reply ↓

  12. Hola José,
    Muy buena tu pagina y tus explicaciones. Quisiera ver si me puedes ayudar…
    Tengo un procedimiento almacenado:
    CREATE DEFINER=`prueba`@`localhost` PROCEDURE `pPRUEBA`(IN `_table` VARCHAR(20), IN `_ids` VARCHAR(200), IN `_primary` VARCHAR(20))
    READS SQL DATA
    BEGIN
    SET @param = _ids;
    SET @sql_ =CONCAT(‘SELECT * FROM ‘,_table,’ WHERE ‘,_primary,’ IN (?)’);
    PREPARE statement_ FROM @sql_;
    EXECUTE statement_ using @param;
    DEALLOCATE PREPARE statement_;
    END

    Desde PHP llamo al procedimiento almacenado ($cnn es la conexion persistente ya establecida y funcionando):
    $param = sprintf(“%s, %s, %s”,
    GetSQLValueString(‘estados’, “text”),
    GetSQLValueString(‘0,1,2,3’, “text”),
    GetSQLValueString(‘IdEstado’, “text”));
    $query = “CALL pPRUEBA (” . $param . “)”;
    $rs = mysqli_query($cnn, $query) or die(mysqli_error($cnn));
    $row = mysqli_fetch_all($rs, MYSQLI_ASSOC);

    Pero el resultado que devuelve es solamente del primer indice pasado como parametro:
    array(1) { [0]=> array(2) { [“IdEstado”]=> string(1) “0” [“Nombre”]=> string(4) “BAJA” } }

    Que estoy haciendo mal?

    Reply ↓

  13. Buenas!!!

    Un problemilla que tengo con lo de aprovechar variables para ejecutar un select.

    Si ejecuto lo siguiente:
    set @_nie := ‘X1234567Z’;
    set @_nif := ‘12345678X’;

    SELECT * FROM tabla WHERE nif IN (@_nie,@_nif);

    Me da el error:
    Error Code: 1270. Illegal mix of collations (utf8_unicode_ci,IMPLICIT), (utf8_general_ci,IMPLICIT), (utf8_general_ci,IMPLICIT) for operation ‘ IN ‘

    El campo nif de la tabla tiene collation utf8_unicode_ci, y entiendo que por defecto las dos variables me pillan collation utf8_general_ci.

    Es posible al hacer el SET, aparte de dar valor a la variable, indicarle un collation determinado?

    Gracias…!!!

    Reply ↓

    • Me acabas de hacer una pregunta súper díficil xDDD ¿has mirado si mysql tiene funciones de conversión?
      O quizás al declararla, cuando haces un PROCEDURE puedes definir las variables así:
      DECLARE test VARCHAR(50) CHARACTER SET 'utf8mb4';

      Reply ↓

  14. A) crea una funacion que dada una comision que se pasara como parametro en la llamada, obtenga el numero de cuidadores cuyo sueldo despues der aplicar esta nueva comision se que 3000.
    b)realiza una llamada a la funcion con una comision 0.8
    C) realiza una llamada guardando el valor obtenido por la funcion en una variable, visualizar dicha variable

    Reply ↓

  15. Hola. Quisiera poder hacer un query en mysql (php), en el que el campo “tabla” sea una variable.
    Es decir por ejemplo:

    SELECT campo1 FROM $var WHERE campo2 = j

    La variable que cambia es la tabla.
    Se hace asi?
    Muchas gracias

    Reply ↓

    • Hola Miguel, sí se haría así. Porqué entiendo que lo quieres hacer desde PHP y con cadenas con comillas dobles.

      Una aviso, si sigues esta estrategía cuidado con el WNERE que las columnas que pongas deben existir en las posibles tablas a consultar.

      Un saludo

      Reply ↓

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

This site uses Akismet to reduce spam. Learn how your comment data is processed.