Conversie (MySQL)

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Doorgaans gaat conversie impliciet: Als je bv. gaat rekenen met tekst-objecten, maakt MySQL er stilletjes numerieke waardes van en vice versa. Soms geeft dat echter foutmeldingen, en dan heb je expliciete conversies nodig. Daar gaat dit artikel over. Twee situaties waarin dat een rol lijkt te spelen:

In een select-statement volstaat impliciete conversie - In een update-statement is expliciete conversie vereist
In een SQL-script volstaat impliciete conversie - In een sproc is expliciete conversie vereist.

Ignore

Door ignore aan update-queries toe te voegen (zie de voorbeelden hieronder), kun je vaak alsnog met impliciete conversie volstaan.

SQL Strict Mode

Standaard staat SQL Strict Mode ingeschakeld. Dat 'strict' heeft betrekking op allerlei data-veranderende operaties (waaronder update-queries). Eén van de situaties waarin strict mode een foutmelding geeft, is als je een operatie uitvoert op een lege string.

Je kunt strict mode uitschakelen middels

 SET SESSION sql_mode = ''

en nog op diverse andere manieren. Zie MySQL Strict Mode & Configuratiebestanden (MySQL) voor details.

Decimaal naar integer - floor

floor(decimaal)

Bv.:

select floor(1.23);

1

Text naar decimaal - cast

Bv.:

> select cast("2.15" as decimal(3,2));
2.15

> select cast("2.15" as decimal(3,2))+1;
3.15

> select cast("blub" as decimal(3,2));   # Werkt dus ook voor textuele input - zonder foutmelding!
0

> select cast("blub" as decimal(3,2))+1;
1

> select cast("4.27 / 8.23" as decimal(3,2));
4.27

> select cast("4.27 / 8.23" as decimal(3,2))+1;
5.27

When the numerical format is too small to contain the resulting value:

> select cast("12.5" as decimal (3,2));
9.99

Waarschijnlijk is 9,99 de waarde is, die het dichts bij het beoogde getal komt, binnen de gespecificeerde precisie.

cast kan ook gebruikt worden in een argument voor sortering. Voorbeeld:

select
   sku_oem,
   dim1_mm_dot,
   dim2_mm_dot,
   dim3_mm_dot
from
   oem_bal
where
   dim1_mm_dot is not null
order by
   cast(dim1_mm_dot as decimal(4,2)) asc,
   cast(dim2_mm_dot as decimal(4,2)) asc,
   cast(dim3_mm_dot as decimal(4,2)) asc;

Text naar decimaal - Truncated value-foutmelding (maart 2018)

In maart 2018 had ik een situatie, dat bepaalde code wel werkte in scripts, maar niet in sprocs. Het ging om deze code: select cast("4.27 / 8.23" as decimal(3,2));. Dit was de foutmelding:

Error Code: 1292. Truncated incorrect DECIMAL value: '4.27 / 8.23'	1,251 sec

Volgens [1] is dit een strict sql-foutmelding: Sommige dingen mogen dan niet.

Oplossing (naast aanpassing strict sql-modules): Gebruik ignore keyword:

update ignore dimensions_tmp
set dim1_mm_period_num = cast(dim1_mm_period_txt as decimal(5,2));

Text naar decimaal - Truncated value-foutmelding (juli 2018)

Het probleem

Dit werkt wel:

select 0.88 * sell_price
from 201806_prices_amazon_de_imp

maar dit werkt niet:

drop table if exists price_tmp;
create table price_tmp
select 0.88 * sell_price as sell_price_uk
from 201806_prices_amazon_de_imp

Aanvullende gegevens

Velden expliciet defineren helpt niet

Code hieronder helpt niet. Dus zelfs als de velden al vantevoren zijn gedefineerd (waardoor de vereiste conversie ondubbelzinnig wordt):

drop table if exists price_tmp;
create table price_tmp
select sku from 201806_prices_amazon_de_imp;

call add_column_unless_exists("price_tmp","sell_price_uk","varchar(10)");

update price_tmp
join 201806_prices_amazon_de_imp on price_tmp.sku = 201806_prices_amazon_de_imp.sku
set sell_price_uk = 201806_prices_amazon_de_imp.sell_price;

select * from price_tmp;

update price_tmp
set sell_price_uk = 0.88*sell_price_uk;   # DIT GEEFT TRUNCATE-FOUTMELDING

Uitprobeersels

Het laatste stukje code hierboven is trouwens de essentie van het probleem. Een paar uitprobeersels:

update price_tmp

   # set sell_price_uk = 0.88*sell_price_uk; # Werkt niet
   # set sell_price_uk = 0.88*cast(sell_price_uk as decimal (5,2)); # Werkt niet
   # set sell_price_uk = cast(0.88*sell_price_uk as char(10)); # Werkt niet, maar om een andere reden!
   set sell_price_uk = cast(floor(0.88*sell_price_uk) as char(10)); # Werkt, maar geen decimalen
   set sell_price_uk = cast(cast(0.88*sell_price_uk as decimal (5,2)) as char(10)); # Waarschijnlijk de oplossing

select * from price_tmp;

De reden waarom set sell_price_uk = cast(0.88*sell_price_uk as char(10)) niet werkt: Je krijgt repeterende breuken, en die passen uiteraard niet in char(10)

Ignore helpt bij één van de problemen

Dit werkt wel, al krijg je waarschuwingen en vermoedelijk ongedefineerde afrondingen:

update ignore price_tmp
    
set sell_price_uk = cast(0.88*sell_price_uk as char(10));

select * from price_tmp;

Bug cast & varchar

Door een bug werkt cast niet bij varchar [2]

Oorzaken

Expliciete conversie numeriek → tekst vereist: In deze situaties is er een expliciete conversie naar een tekst-format vereist. Zonder zo'n conversie krijg je een foutmelding
Afronding vereist vóór conversie numeriek → tekst: In situaties dat je een repeterende breuk krijgt, kun je deze niet zondermeer converteren naar een numeriek data-type, want dat gaat nooit passen.

Oplossing?

Ik ben er nog steeds niet helemaal uit, want dit werkt niet zonder ignore, en er gebeuren vermoedelijke ongedefineerde dingen rondom afronding:

drop table if exists price_tmp;
create table price_tmp
select sku from 201806_prices_amazon_de_imp;

call add_column_unless_exists("price_tmp","sell_price_uk","char(10)");

update price_tmp
join 201806_prices_amazon_de_imp on price_tmp.sku = 201806_prices_amazon_de_imp.sku
set sell_price_uk = 201806_prices_amazon_de_imp.sell_price;

update ignore price_tmp
set sell_price_uk = cast(cast(0.88*sell_price_uk as decimal (5,2)) as char(10));

Niet zomaar tekst aan decimale velden toekennen? (juli 2018)

Probleem

Waarschijnlijk opnieuw een voorbeeld, dat je in een select-statement impliciete conversie hebt, waardat bij een update-statement geweigerd wordt:

################################################################################
# Create basic table
################################################################################
#
drop table if exists price_tmp;
create table price_tmp
select 
	sku as sku,
    sell_price as sell_price_01_euro_txt
from 201806_prices_amazon_de_imp;

################################################################################
# Add column with numerical prices
################################################################################
#
call add_column_unless_exists("price_tmp","sell_price_01_euro_num","decimal(10,2)");

update price_tmp
set sell_price_01_euro_num = sell_price_01_euro_txt;

Het werkt wel als je ignore toevoegt aan het laatste statement:

update ignore price_tmp
set sell_price_01_euro_num = sell_price_01_euro_txt;   # FOUTMELDING

Oorzaak & oplossing

Er bleek een niet-numerieke waarde in dit veld te zitten en strict mode was ingeschakeld. Oplossingen:

Strict Mode uitschakelen
Die niet-numerieke waarde verwijderen.