Generated MySQL Columns and Changing Values

I was speaking at PHP[Tek] this week on the JSON Data Type and using generated columns. JSON columns can not be indexed but data from a JSON column can be extracted via a generated column and that column can be indexed. All was going well until someone asked me about modifying data in a generated column. Was it possible?

I blinked hard. I have not tried that! I had not seen any mention of that in the documentation. So I had to admit that I did not know and would have to try that.

The Test


mysql> CREATE TABLE gentest (a INT, b INT AS (a + 1) STORED, INDEX(b));
...
mysql> INSERT INTO gentest VALUES (1),(2),(3),(4);
...
So now we have a table with data to test. So lets try to modify the value of one of the generated columns.

mysql> UPDATE gentest SET b = 9 WHERE a = 1;

And what did the server do?

It returned an error and told me The value specified for generated column 'b' in table 'gentest' is not allowed.

Lesson Learned

So now I know that the server will not allow you to mangle, er, change the value of generated columns away from their definition. And yes, I find the same thing with both the VIRTUAL and STORED versions of generated columns.

Nhận xét

Bài đăng phổ biến từ blog này

Using MySQL to Output JSON

Pretty JSON with JSON_PRETTY()

Using find() with the MySQL Document Store

php


Save 50% on frames when you sign up for the GlassesShop.com newsletter!
Free Shipping At GlassesShop on orders $49+ ! No code required -
Weekly Deals starting at $6.95 plus an extra 20% off on orders $39+ At GlassesShop.com!
BUY ONE GET ONE FREE– use coupon code GSBOGO At GlassesShop.com - Ends 12/31/18
First Pair Free! Use Coupon Code FIRSTFREE At GlassesShop.com – Expires 12/31/2018
New Year, New Savings! Buy one Get one FREE! Use Code GSBOGO Details At GlassesShop.com
Best Buy Co, Inc.
Best Buy Co, Inc.
Best Buy Co, Inc.
button
banner
American National Standards Institute Inc.
SM_125x125button
SM_468x60banner
Iolo technologies, LLC
US/EU Warehouse Super Deal + Extra 10% OFF Code(GOFUN)
Up to 40% OFF + Extra 10% OFF Indoor & Patio Furniture(Code: fubuying)
Thousands of Car Accessories@TOMTOP.com
Thousands of Car Accessories@TOMTOP.com





























Seleção especial para os fãs de Harry Potter! Livros e artigos diversos inspirados na história de Harry, Hermione, Rony e cia!
Cupom exclusivo para os livros da série Harry Potter - CUPOM = POMODEOURO
Dia das crianças Submarino, até 10% off em brinquedos - CUPOM = VEMPROPLAY
10% off em telefonia - CUPOM = ALO10
Home
submarino.com.br
submarino.com.br
Planeta Criança - Diversos produtos com desconto para o Dia das Crianças
Zizo
banner
button
Lançamento FIFA 18 Edição Standard Microsoft Store Brazil
Lançamento FIFA 18 Edição Standard Microsoft Store Brazil




Contentmart
Hire - 468 x 60banner
Article in $3 - 125x125button
Os produtos mais vendidos na Multiar!
Home

Multiar
Home





Mochilas para Notebook Samsonite com Frete Grátis Sul e Sudeste
Conversor e gravador digital com Frete Grátis Brasil (exceto Norte)
Mouses HP com Frete Grátis Sul e Sudeste
WalmartBR
WalmartBR
WalmartBR
WalmartBR
HOMEPAGE
NOTEBOOKS
ALL IN ONE
IMPRESSORAS E SCANNERS
CARTUCHOS E TONERS
ACESSÓRIOS



Nossa melhor proteção para PC, Mac ou dispositivos móveis De R$ 109,00 por R$ 69,00
Nossa melhor proteção para todas as suas formas de se conectar De R$ 169,00 por R$ 99,00
Nossa melhor proteção, mais backup de PC e recursos de segurança da família para você e as pessoas importantes na sua vida De R$ 249,00 por R$ 139,00
Symantec Brazil
Symantec Brazil
Os produtos mais vendidos no ShopFácil!
HOME ShopFacil


eGlobal Central
GenericPro_1.jpgbanner
Relógios -5% de desconto no - CUPOM = FDA5-FBA4-582B-19D3




Informática