Conversión de tipos de cadena y numéricos en MySQL: CAST vs CONVERT explicado con ejemplos prácticos

1. Introducción

Al trabajar con una base de datos MySQL, con frecuencia encontrará situaciones como “los datos que deberían tratarse como números están almacenados como tipo cadena” o “no se pueden realizar cálculos y agregaciones mientras los datos siguen siendo una cadena”. Por ejemplo, los datos importados desde Excel o archivos CSV a menudo se guardan como VARCHAR o CHAR, aunque representan valores numéricos. En esos casos, operaciones como suma, promedio y comparaciones numéricas pueden no funcionar como se espera, lo que dificulta escribir consultas SQL correctas.

Por otro lado, existen escenarios en los que los datos numéricos deben manejarse como cadenas. Por ejemplo, al rellenar con ceros un ID o código, o al concatenar datos numéricos con otras columnas para mostrarlos. En tales casos, es necesario convertir los tipos numéricos a tipos cadena.

De este modo, “la conversión de tipos entre cadenas y números” es una de las habilidades fundamentales para manipular datos de forma flexible en MySQL. Al usar la conversión de tipos de manera adecuada, puede mantener la integridad de los datos y habilitar agregaciones y procesamientos flexibles.

En este artículo explicaremos claramente cómo convertir entre cadenas y números en MySQL, cómo utilizar estas técnicas de forma eficaz, y los errores comunes y buenas prácticas basadas en la experiencia real. Tanto si es principiante como si es un desarrollador SQL experimentado, esta guía le aportará valor práctico.

2. Tipos de conversión de tipos en MySQL

MySQL ofrece varias funcionalidades convenientes para convertir tipos de datos. En esta sección explicaremos dos patrones principales: “conversión de tipo explícita” y “conversión de tipo implícita”, junto con sus métodos representativos.

2.1 Conversión de tipo explícita

La conversión de tipo explícita consiste en especificar claramente en SQL que “este valor debe convertirse a un tipo específico”. Las dos funciones siguientes se utilizan con frecuencia.

Función CAST
CAST() es una función estándar de SQL que convierte un valor a un tipo especificado. Se usa ampliamente en MySQL.

SELECT CAST('123' AS SIGNED);

En este ejemplo, la cadena '123' se convierte en un entero con signo (SIGNED). Otros tipos convertibles incluyen UNSIGNED (entero sin signo), DECIMAL (número de punto fijo), CHAR (cadena) y DATE (fecha).

Función CONVERT
CONVERT() es otra función utilizada para convertir valores a diferentes tipos de datos. La sintaxis es la siguiente.

SELECT CONVERT('456', UNSIGNED);

En este ejemplo, la cadena '456' se convierte en un entero sin signo (UNSIGNED). La principal diferencia con CAST() es que CONVERT() también puede usarse para la conversión de juegos de caracteres.

2.2 Conversión de tipo implícita

La conversión de tipo implícita es un mecanismo mediante el cual MySQL convierte automáticamente los tipos de datos al realizar operaciones o comparaciones.

Por ejemplo, al sumar un número y una cadena, MySQL convierte automáticamente la cadena a un valor numérico.

SELECT 1 + '2';
-- Result: 3

De manera similar, al concatenar valores numéricos como cadenas:

SELECT CONCAT(10, ' apples');
-- Result: '10 apples'

La conversión de tipo implícita es conveniente, pero puede producir resultados no deseados. Por lo tanto, para lógica compleja o procesos críticos, se recomienda encarecidamente usar conversión de tipo explícita.

3. Ejemplos prácticos: conversión de cadenas a números

En MySQL, cuando los valores numéricos se almacenan como cadenas (por ejemplo CHAR o VARCHAR), no se pueden realizar cálculos o comparaciones numéricas de forma fiable tal cual. Para agregar y analizar esos datos correctamente, es necesario convertir las cadenas a tipos numéricos. Esta sección presenta los métodos de conversión más usados y las precauciones clave.

3.1 Conversión con la función CAST

El enfoque más básico es usar la función CAST(). Por ejemplo, para convertir la cadena '100' en un entero, escriba lo siguiente:

SELECT CAST('100' AS SIGNED) AS numeric_result;
-- Result: 100 (integer)

Usa SIGNED para enteros con signo y UNSIGNED para enteros sin signo. Para datos decimales, también puedes usar DECIMAL o FLOAT.

SELECT CAST('123.45' AS DECIMAL(10,2)) AS decimal_result;
-- Result: 123.45

3.2 Conversión con la función CONVERT

La función CONVERT() se puede usar de casi la misma manera:

SELECT CONVERT('200', SIGNED) AS converted_result;
-- Result: 200

Ambos producen el mismo resultado, pero como CAST() es SQL estándar y ofrece mejor portabilidad, generalmente se recomienda cuando hay dudas.

3.3 Conversión implícita mediante aritmética

Cuando realizas aritmética con tipos numéricos y de cadena en expresiones SQL, MySQL convierte automáticamente la cadena a un número. Por ejemplo:

SELECT '50' + 25 AS total;
-- Result: 75

También puedes aplicar este comportamiento a funciones de agregación. Por ejemplo, si pasas una columna de tipo cadena a SUM(), MySQL intenta la conversión numérica automáticamente y suma los valores:

SELECT SUM(amount) FROM sales_data;
-- Even if the amount column is VARCHAR, MySQL will attempt numeric summation

3.4 Ten cuidado con cadenas con ceros a la izquierda y valores no numéricos

Las cadenas con ceros a la izquierda (p. ej., '000100') también se pueden convertir a números:

SELECT CAST('000100' AS SIGNED) AS converted_result;
-- Result: 100

Sin embargo, ten cuidado si la cadena contiene caracteres no numéricos. Si conviertes algo como CAST('abc123' AS SIGNED), y no hay caracteres numéricos al inicio, MySQL devuelve 0. Dependiendo de la calidad de los datos, es importante realizar validación de entrada antes de la conversión.

3.5 Casos de uso comunes en el mundo real

• Agregando datos de ventas o montos que se convirtieron en cadenas debido a importaciones de Excel/CSV
• Ordenando IDs almacenados como cadenas (aunque representen números) en orden numérico
• Ordenando cadenas de fechas almacenadas en formato YYYYMMDD por orden de fecha (explicado más adelante)

4. Ejemplos prácticos: Convirtiendo números a cadenas

En MySQL, hay muchos casos en los que quieres tratar datos numéricos como cadenas. Ejemplos típicos incluyen mostrar IDs o códigos con ceros a la izquierda, o construir mensajes concatenando valores numéricos con otro texto. Esta sección introduce métodos representativos y casos de uso prácticos.

4.1 Conversión con la función CAST

Para convertir explícitamente un tipo numérico (como INT o DECIMAL) en un tipo de cadena, usa CAST():

SELECT CAST(123 AS CHAR) AS string_result;
-- Result: '123'

Con este método, puedes concatenar fácilmente columnas numéricas con otras cadenas.

4.2 Conversión con la función CONVERT

También puedes hacer lo mismo con CONVERT():

SELECT CONVERT(456, CHAR) AS converted_result;
-- Result: '456'

Hay poca diferencia con CAST(), pero desde la perspectiva de SQL estándar, CAST() es ligeramente preferido.

4.3 Conversión implícita mediante concatenación

Cuando concatenas números y cadenas usando funciones como CONCAT(), MySQL convierte automáticamente los números en cadenas.

SELECT CONCAT(2024, ' year') AS fiscal_year_display;
-- Result: '2024 year'

Esta conversión implícita se usa comúnmente para salida de informes rutinarios y formateo de datos.

4.4 Casos de uso comunes en el mundo real

• Generando IDs con ceros a la izquierda Para mostrar un ID numérico en 5 dígitos, combínalo con LPAD() de la siguiente manera: SELECT LPAD(CAST(id AS CHAR), 5, '0') AS zero_padded_id FROM users; -- Si id=7, el resultado es '00007'
• Concatenando fechas o montos en cadenas SELECT CONCAT('The total amount is ', CAST(total AS CHAR), ' yen.') AS message FROM orders; -- Si total=1500, el resultado es 'The total amount is 1500 yen.'

4.5 Notas

Aunque la cadena convertida se vea igual visualmente, el comportamiento de “ordenación” y “operaciones de comparación” cambia.
Por ejemplo, al ordenar como cadenas, '20' puede venir antes que '100' (orden lexicográfico). Es importante elegir el enfoque dependiendo de tu propósito.

5. Casos de Uso Avanzados con Conversión de Tipo

La conversión de tipo no se limita a transformaciones numéricas o de cadena simples. También puede aplicarse en diversos escenarios prácticos en sistemas del mundo real. Esta sección presenta casos avanzados de uso comunes y sus consideraciones clave.

5.1 Comparar y Convertir Cadenas con Formato de Fecha

Si su base de datos almacena fechas en formatos como YYYYMMDD como tipos numéricos o de cadena, la comparación simple de cadenas puede no comportarse siempre como se espera.

En esos casos, convertir el valor a un tipo numérico usando CAST() permite una ordenación y comparación cronológica correctas.

SELECT *
FROM events
ORDER BY CAST(event_date AS UNSIGNED);
-- Values such as '20240501', '20240502', etc. are sorted in date order

También puede combinar esto con la función REPLACE() para convertir fechas separadas por guiones como '2024-05-01' en enteros.

SELECT CAST(REPLACE('2024-05-01', '-', '') AS UNSIGNED);
-- Result: 20240501

5.2 Ordenar Tipos ENUM o Valores de Código Numéricamente

Para tipos ENUM o valores de código que representan un significado numérico, es posible que desee ordenarlos en orden numérico en lugar de orden lexicográfico. Al convertirlos a tipos numéricos con CAST() antes de ordenar, se obtiene una clasificación intuitiva.

SELECT *
FROM products
ORDER BY CAST(product_code AS UNSIGNED);

5.3 Uso en Agregación y Formateo de Datos

Por ejemplo, si los importes de ventas se almacenan como VARCHAR, puede obtener un total preciso convirtiéndolos explícitamente dentro de SUM().

SELECT SUM(CAST(sales_amount AS SIGNED)) AS total_sales
FROM sales_data;

5.4 Evitar Problemas con Precisión y Selección de Tipo

Para valores decimales o numéricos grandes, convertir al tipo DECIMAL ayuda a preservar la precisión.

Elegir entre SIGNED y UNSIGNED también es importante, según si pueden existir valores negativos en sus datos.

SELECT CAST('1234.567' AS DECIMAL(10, 3));
-- Result: 1234.567

5.5 Detectar Errores o Datos Inválidos

Si la conversión produce valores inesperados NULL o 0, eso puede indicar datos no válidos.

Por ejemplo, si una cadena no puede convertirse en un número, CAST() puede devolver 0 o NULL dependiendo del modo SQL.

En esos casos, verificar los resultados de la conversión puede ayudar a identificar necesidades de limpieza o validación de datos.

SELECT original, CAST(original AS SIGNED) AS converted
FROM test_data
WHERE CAST(original AS SIGNED) = 0 AND original <> '0';
-- Extract only data that cannot be properly converted to numeric values

6. Diferencias entre CAST y CONVERT

Al realizar conversiones de tipo en MySQL, normalmente se usa la función CAST o la función CONVERT. Aunque parecen similares, existen diferencias en su uso y características. Esta sección explica esas diferencias y cómo elegir entre ellas.

6.1 Diferencias Básicas

• Función CAST Utiliza la sintaxis CAST(valor AS tipo) para convertir explícitamente un valor al tipo especificado. Está definida en el SQL estándar y funciona de manera similar en muchos sistemas de bases de datos. SELECT CAST('123' AS SIGNED);
• Función CONVERT Utiliza la sintaxis CONVERT(valor, tipo) para convertir un valor al tipo especificado. En MySQL, también puede usarse para la conversión de juegos de caracteres. SELECT CONVERT('123', SIGNED); -- Conversión de tipo
SELECT CONVERT('hello' USING utf8mb4); -- Conversión de juego de caracteres

6.2 Compatibilidad con el SQL Estándar

Dado que CAST forma parte del estándar internacional SQL, ofrece mayor portabilidad a otras bases de datos (como PostgreSQL, SQL Server y Oracle). Por otro lado, CONVERT incluye extensiones específicas de MySQL, particularmente para la conversión de juegos de caracteres, usando una sintaxis como CONVERT(expr USING charset_name).

6.3 Cómo Elegir Entre Ellas

• Para la conversión de tipos (números, cadenas, fechas, etc.) En la mayoría de los casos, usar CAST es la opción más segura. Es SQL estándar, altamente portable y adecuada para futuras migraciones de bases de datos.
• Para la conversión de juego de caracteres (p. ej., sjis a utf8mb4) Debe usar CONVERT(expr USING charset_name).
• Para casos específicos de MySQL o especiales CONVERT puede ofrecer flexibilidad adicional, pero para propósitos generales, comience con CAST y use CONVERT solo cuando sea necesario.

6.4 Comparación de muestra

-- Type conversion using CAST (convert to integer)
SELECT CAST('456' AS SIGNED);

-- Type conversion using CONVERT
SELECT CONVERT('456', SIGNED);

-- Character set conversion using CONVERT
SELECT CONVERT('Hello' USING utf8mb4);

6.5 Notas importantes

• Si la conversión falla, ambas funciones pueden devolver NULL o 0.
• La conversión de juego de caracteres no se puede realizar con CAST.
• El comportamiento puede variar según el modo SQL o la versión de MySQL, por lo que siempre se debe probar en entornos de desarrollo y producción.

7. Notas importantes y mejores prácticas

Al usar la conversión de tipos de cadena y numéricos en MySQL, existen varios posibles inconvenientes que deben tenerse en cuenta. Esta sección presenta mejores prácticas para ayudar a prevenir problemas y garantizar un uso seguro y preciso.

7.1 Errores o NULL/0 inesperados por conversiones inválidas

Al realizar una conversión de tipo, si el valor de origen no tiene el formato correcto, MySQL puede devolver valores inesperados NULL o 0.

SELECT CAST('abc' AS SIGNED) AS result;
-- Result: 0 (default MySQL behavior)

Como se muestra arriba, convertir una cadena no numérica a un tipo numérico produce 0. Por lo tanto, siempre verifique que los datos originales no contengan valores inválidos antes de la conversión.

Dependiendo de la configuración del modo SQL, la conversión puede producir un error o NULL. Siempre confirme la configuración de su entorno de producción.

7.2 Elección de precisión y tipos con/sin signo

• Para valores que contienen decimales, conviértalos a DECIMAL o FLOAT.
• Si los valores negativos son posibles, use SIGNED; si los valores son estrictamente enteros positivos, use UNSIGNED.

Siempre seleccione el tipo apropiado según las características de sus datos.

7.3 Impacto en los índices

Usar funciones de conversión de tipo como CAST o CONVERT en cláusulas WHERE o ORDER BY puede impedir que se utilicen índices, lo que resulta en una degradación del rendimiento.

SELECT * FROM users WHERE CAST(user_id AS SIGNED) = 1000;
-- Even if user_id has an index, it is often not used

Para conjuntos de datos grandes o consultas críticas en rendimiento, es mejor unificar los tipos de columna en el diseño del esquema para evitar conversiones innecesarias.

7.4 No dependa excesivamente de la conversión implícita

La conversión de tipo implícita en MySQL es conveniente, pero puede generar comportamientos no deseados. Para lógica importante, siempre use CAST o CONVERT explícitos.

SELECT '100a' + 20;
-- Result: 100 (only the leading numeric portion is used)

Para evitar errores sutiles e inconsistencias de datos, convierta explícitamente como hábito.

7.5 Validar tipos en la etapa de entrada de datos

Al diseñar su base de datos, almacene los datos numéricos como tipos numéricos y los datos de cadena como tipos de cadena. Minimizar la necesidad de conversión mediante un diseño de esquema adecuado es una de las formas más efectivas de prevenir problemas.

8. Resumen

La conversión de tipos entre cadenas y números en MySQL es un tema inevitable en el procesamiento diario de datos, agregación y operaciones del sistema. En este artículo, cubrimos todo, desde el uso básico hasta técnicas avanzadas y precauciones importantes.

Para la conversión de cadena a número, existen métodos explícitos como CAST y CONVERT. Aunque la conversión implícita se usa frecuentemente en la práctica, priorizar la conversión explícita mejora la fiabilidad y la calidad de los datos. Por otro lado, la conversión de número a cadena también se usa ampliamente para formatear IDs, códigos y generar mensajes de salida.

Además, técnicas como aplicaciones relacionadas con fechas, agregación y ordenación, y detección de errores demuestran cuán poderosa puede ser la conversión de tipos en escenarios del mundo real. Sin embargo, la conversión también conlleva posibles inconvenientes e impactos en el rendimiento. Siempre siga las mejores prácticas discutidas anteriormente.

Al dominar correctamente la conversión de tipos, puede ampliar significativamente su capacidad para manipular datos de manera eficaz en MySQL.
Esperamos que este artículo respalde su trabajo diario y su aprendizaje.

9. Preguntas frecuentes (FAQ)

Q1. ¿Qué ocurre si utilizo CAST en una cadena como ‘abc’ a un número?

En MySQL, al intentar convertir una cadena a un tipo numérico (como SIGNED o UNSIGNED), MySQL devuelve la parte numérica inicial si está presente; de lo contrario, devuelve 0.

SELECT CAST('abc' AS SIGNED); -- Result: 0
SELECT CAST('123abc' AS SIGNED); -- Result: 123

Sin embargo, dependiendo del modo SQL (por ejemplo, STRICT_TRANS_TABLES), el resultado puede ser un error o NULL.

Q2. ¿Debo usar CAST o CONVERT?

Para la conversión de tipos general (números, cadenas, fechas, etc.), se recomienda usar CAST porque se ajusta al estándar SQL. Para conversiones de juego de caracteres, debe utilizar la función CONVERT con la cláusula USING. Elija según su caso de uso.

Q3. ¿Es suficiente la conversión implícita de tipos?

La conversión implícita puede funcionar para consultas pequeñas o pruebas, pero para agregaciones críticas y desarrollo de sistemas de producción, se recomienda encarecidamente la conversión explícita (CAST o CONVERT). Escribir conversiones explícitas ayuda a prevenir comportamientos no deseados y errores.

Q4. ¿El uso de conversión de tipos en WHERE o ORDER BY desactiva los índices?

Sí. Aplicar funciones como CAST o CONVERT a una columna puede impedir que MySQL utilice los índices definidos sobre esa columna.
Si se requiere una consulta de alto rendimiento sobre grandes conjuntos de datos, unifique los tipos de columna con antelación o considere usar subconsultas o columnas generadas.

Q5. ¿Es segura la conversión de tipos para decimales o números grandes?

Usar DECIMAL o FLOAT puede preservar la precisión, pero aún pueden producirse errores de redondeo, pérdida de precisión o truncamiento. Siempre especifique la precisión y escala suficientes para sus requisitos.

Q6. ¿Cuál es la diferencia entre ordenar como cadena y ordenar como número?

Al ordenar como cadenas, valores como '10' pueden aparecer antes que '2' debido al orden lexicográfico. Si desea un orden numérico, siempre realice la conversión de tipos antes de ordenar.