Tabel aanmaken (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
Regel 12: | Regel 12: | ||
index(sku_oem) | index(sku_oem) | ||
) | ) | ||
− | + | collate utf8mb4_0900_ai_ci; | |
+ | |||
</pre> | </pre> | ||
Versie van 12 aug 2020 18:17
Wat ik ongeveer zoek
create table tmp ( sku varchar(255) comment "SKU's standaard 255 tekens", sku_oem varchar(255), ean varchar(40) comment "Minimaal 40 tekens, ivm. headers", note text comment "Let op: Text-veld kunnen niet zomaar geïndexeerd worden", primary key (sku), index(sku_oem) ) collate utf8mb4_0900_ai_ci;
mysqldump
Hoe tabel-definities eruit zien in een mysql-dump:
create table if not exists tbl_content_sjabloon ( productnaam varchar(60) CHARACTER SET utf8 DEFAULT NULL, referentie varchar(8) CHARACTER SET utf8 DEFAULT NULL, ean varchar(3) CHARACTER SET utf8 DEFAULT NULL, producttype varchar(255) DEFAULT NULL, beschrijving text CHARACTER SET utf8, afbeelding varchar(55) CHARACTER SET utf8 DEFAULT NULL, merk varchar(255) DEFAULT NULL, gewicht varchar(7) CHARACTER SET utf8 DEFAULT NULL, gewichteenheid varchar(5) DEFAULT NULL, mpn varchar() CHARACTER SET utf8 DEFAULT NULL, aantalstuks varchar(29) CHARACTER SET utf8 DEFAULT NULL, ) ENGINE=InnoDB DEFAULT CHARSET=latin;
Merk op dat dit er verdacht uitziet: Default charset is latin, maar vrijwel alle kolommen zijn utf8.
PK
create table tbl_main_tool ( pk varchar(100) character set utf8 not null primary key, merk varchar(100) character set utf8 default null, apparaat_type varchar(100) character set utf8 default null, apparaat_versie varchar(100) character set utf8 default null, soort varchar(100) character set utf8 default null );
Auto-incrementele primaire sleutel
create table if not exists tbl_ads ( pk mediumint auto_increment, # Ook bij auto_increment moet je het datatype specificeren headline varchar(25), line1 varchar(35), line2 varchar(35), displayurl varchar(35), desturl varchar(100), finalurl varchar(100), memo varchar(255), primary key(pk) );
Indices
Index specificeren tijdens tabel-definitie:
CREATE TABLE `dwh`.`new_table` ( `idnew_table` INT NOT NULL, `new_tablecol` VARCHAR(45) NOT NULL, `new_tablecol1` VARCHAR(45) NOT NULL, PRIMARY KEY (`idnew_table`), INDEX `index2` (`new_tablecol` ASC));
Of later toevoegen:
create table orgbrush_tmp ( sku varchar(255), orgbrush_id text, orgbrush_ean text, orgbrush_id_or_ean text, # Simple coalesce orgbrush_id_and_or_ean text # Contains both, if available ); alter table orgbrush_tmp add index(sku);
Voorbeeld: Gecombineerde PK + indices
drop table if exists device_sku_int; create table device_sku_int ( brand varchar(255), # Device brand, e.g. Bosch device_type varchar(255), # Machinereferentie, e.g. 10GM/P device_sku varchar(255), # Device SKU, assigned by brand device_kind_nl varchar(255), # E.g. "Boormachine" device_voltage varchar(40), device_other varchar(255), device_kind varchar(40), device_lang varchar(40), sku varchar(255), cat varchar(255), # Just a field from source tables (March 2020) note text, # Just a field from source tables (March 2020) # Primary key ############################################## # primary key(brand, device_type, sku), # index (sku) ########################## # # * Used in join with root_tmp at sq_prepare_brands_per_sku. This index reduces # This index reduces run-time of a specifc grouping-query from 7.9s to 0.08s! # * Although sku is part of the PK, it doesn't function as an index, as it isn't # the first field in the PK # index (sku), # index (device_kind_nl) ########################## # # Near the end, emtpy instances of this field are filled with a value # and that takes a couple of seconds (without index)! # index (device_kind_nl) # Test: Does this improve performance? > Makes it worse! ######################################################## # # index (device_type), # index (device_sku), # index (brand), # index (device_kind) );
Commentaar
create table blub_tmp ( sku varchar(255) comment 'Dit is het commentaar-veld', blub varchar(255), note text, primary key (sku), index (blub) )
NN - not null
Indien je specificeert dat een veld niet null mag zijn, kreeg je daar geen ongedefineerde waardes. Maar mogelijk wel lege velden krijgen. Bv.:
drop table if exists device_sku_int; create table device_sku_int ( brand varchar(255) not null, # Device brand, e.g. Bosch device_type varchar(255) not null, # Machinereferentie, e.g. 10GM/P device_sku varchar(255), # Device SKU, assigned by brand device_kind_nl varchar(255), device_voltage varchar(40) );
Charset & collation
[1]:
create table blub ( pk varchar(40), v2 varchar(255) )