mysql - 以编程方式重新排序 SQL 列？

Question

我有一些 SQL 代码来创建我的表，其中所有列名都按字母顺序排列。我需要使用 SQL DATA LOADINFILE 加载的数据采用完全不同的格式。我可能有 1000 列需要重新排序以匹配我的 CSV。

我正在尝试找到一种自动执行此操作的方法。 SQL代码为:

CREATE TABLE rets_property_cnd (
`AgentCoListFirstName` VARCHAR(50) COMMENT 'Co-Listing Agent First Name',
`AgentCoListFullName` VARCHAR(75) COMMENT 'Co-Listing Agent Full Name',
`AgentCoListID` VARCHAR(8) COMMENT 'Co-Listing Agent ID ',
`AgentCoListLastName` VARCHAR(50) COMMENT 'Co-Listing Agent Last Name',
`AgentCoListPhone` VARCHAR(25) COMMENT 'Co-Listing Agent Phone',
`AgentListFirstName` VARCHAR(50) COMMENT 'Listing Agent First Name',
`AgentListFullName` VARCHAR(75) COMMENT 'Listing Agent Full Name',
`AgentListID` VARCHAR(8) COMMENT 'Listing Agent ID',
`AgentListLastName` VARCHAR(50) COMMENT 'Listing Agent Last Name',
`AgentListPhone` VARCHAR(25) COMMENT 'Listing Agent Phone',
`AppliancesOther` TEXT COMMENT 'Appliances Other',
`AreaID` VARCHAR(50) COMMENT 'Area',
`AssociationFee` INT(3) COMMENT 'Association Fee',
`AssociationFeeIncludes` TEXT COMMENT 'Association Fee Includes',
`BasementDesc` VARCHAR(50) COMMENT 'Basement Description',
`BasementType` VARCHAR(50) COMMENT 'Basement Type',
`BedroomFourDim` VARCHAR(5) COMMENT 'Bedroom 4 Dimensions',
`BedroomOneDesc` VARCHAR(50) COMMENT 'Bedroom 1 Description',
`BedroomOneDim` VARCHAR(5) COMMENT 'Bedroom 1 Dimensions',
`BedroomsBasement` INT(2) COMMENT 'Bedrooms Basement',
`BedroomsMain` INT(2) COMMENT 'Bedrooms Main',
`BedroomsOther` INT(2) COMMENT 'Bedrooms Other',
`BedroomsSecond` INT(2) COMMENT 'Bedrooms Second',
`BedroomsThird` INT(2) COMMENT 'Bedrooms Third',
`BedroomThreeDim` VARCHAR(5) COMMENT 'Bedroom 3 Dimensions',
`BedroomTwoDesc` VARCHAR(50) COMMENT 'Bedroom 2 Description',
`BedroomTwoDim` VARCHAR(5) COMMENT 'Bedroom 2 Dimensions',
`BuiltInfo` VARCHAR(50) COMMENT 'Built Information',
`City` VARCHAR(20) COMMENT 'City',
`CommunityAmenities` TEXT COMMENT 'Community Amenities',
`CompletionDate` DATE default '0000-00-00' not null COMMENT 'Completion Date',
`ConstructionType` VARCHAR(50) COMMENT 'Construction Type',
`ContingencyType` VARCHAR(50) COMMENT 'Contingency Type',
`CoolingSource` VARCHAR(50) COMMENT 'Cooling Source',
`CoolingSystem` VARCHAR(50) COMMENT 'Cooling System',
`CountyID` VARCHAR(50) COMMENT 'County',
`DenDesc` VARCHAR(50) COMMENT 'Den Description',
`DenDim` VARCHAR(5) COMMENT 'Den Dimensions',
`DiningRoomDesc` VARCHAR(50) COMMENT 'Dining Room Description',
`DiningRoomDim` VARCHAR(5) COMMENT 'Dining Room Dimensions',
`Directions` VARCHAR(255) COMMENT 'Directions',
`DrapesRemain` VARCHAR(50) COMMENT 'Drapes Remain',
`DrivewayType` VARCHAR(50) COMMENT 'Driveway Type',
`ElementarySchool` VARCHAR(50) COMMENT 'Elementary School',
`ElementarySchoolTwo` VARCHAR(50) COMMENT 'Elementary School 2',
`EnergyFeatures` TEXT COMMENT 'Green Features',
`ExteriorAlterations` CHAR(1) COMMENT 'Exterior Alterations',
`ExteriorTrim` VARCHAR(50) COMMENT 'Exterior Trim',
`FloorTypes` TEXT COMMENT 'Floor Types',
`FullBathsBasement` INT(2) COMMENT 'Full Baths Basement',
`FullBathsMain` INT(2) COMMENT 'Full Baths Main',
`FullBathsOther` INT(2) COMMENT 'Full Baths Other',
`FullBathsSecond` INT(2) COMMENT 'Full Baths Second',
`FullBathsThird` INT(2) COMMENT 'Full Baths Third',
`GarageCapacity` INT(1) COMMENT 'Garage Capacity',
`GarageDescription` VARCHAR(50) COMMENT 'Garage Description',
`GreenCertifyingBody` TEXT COMMENT 'Green Certifying Body',
`HalfBathsBasement` INT(2) COMMENT 'Half Baths Basement',
`HalfBathsMain` INT(2) COMMENT 'Half Baths Main',
`HalfBathsOther` INT(2) COMMENT 'Half Baths Other',
`HalfBathsSecond` INT(2) COMMENT 'Half Baths Second',
`HalfBathsThird` INT(2) COMMENT 'Half Baths Third',
`HandicapAccess` VARCHAR(50) COMMENT 'Handicap Accessible',
`HasPetRestriction` CHAR(1) COMMENT 'Has Pet Restriction',
`HeatingSource` VARCHAR(50) COMMENT 'Heating Source',
`HeatingSystem` VARCHAR(50) COMMENT 'Heating System',
`HighSchool` VARCHAR(50) COMMENT 'High School',
`HobbyRoomDesc` VARCHAR(50) COMMENT 'Hobby Room Description',
`HobbyRoomDim` VARCHAR(5) COMMENT 'Hobby Room Dimensions',
`InteriorOther` TEXT COMMENT 'Interior Other',
`IsCertifiedGreen` CHAR(1) COMMENT 'Certified Green Home',
`IsNewConstruction` CHAR(1) COMMENT 'New Construction',
`JuniorHighSchool` VARCHAR(50) COMMENT 'Junior High School',
`KitchenDesc` VARCHAR(50) COMMENT 'Kitchen Description',
`KitchenDim` VARCHAR(5) COMMENT 'Kitchen Dimensions',
`Latitude` DECIMAL(19,16) COMMENT 'Latitude',
`ListingStatusID` VARCHAR(50) COMMENT 'Listing Status',
`ListingType` VARCHAR(50) COMMENT 'Listing Type',
`ListPrice` INT(10) COMMENT 'List Price',
`LivingRoomDesc` VARCHAR(50) COMMENT 'Living Room Description',
`LivingRoomDim` VARCHAR(5) COMMENT 'Living Room Dimensions',
`Longitude` DECIMAL(19,16) COMMENT 'Longitude',
`MailboxType` VARCHAR(50) COMMENT 'Mailbox Type',
`MapNumber` VARCHAR(4) COMMENT 'Map Number',
`MediaUpdateDate` DATETIME default '0000-00-00 00:00:00' not null COMMENT 'Media Update Date',
`MiscOther` TEXT COMMENT 'Miscellaneous Other',
`MlsNum` VARCHAR(8) COMMENT 'MLS Number',
`ModDate` DATETIME default '0000-00-00 00:00:00' not null COMMENT 'Mod Date',
`NextOpenHouseDate` DATETIME default '0000-00-00 00:00:00' not null COMMENT 'NextOpenHouseDate',
`NumberOfFireplaces` INT(1) COMMENT 'Number Of Fireplaces',
`NumOfStories` DECIMAL(3,2) COMMENT 'Number Of Stories',
`OfficeCoListCode` VARCHAR(8) COMMENT 'Co-Listing Office Code',
`OfficeCoListCompanyID` VARCHAR(8) COMMENT 'Co-Listing Office Company ID',
`OfficeCoListID` VARCHAR(8) COMMENT 'Co-Listing Office ID',
`OfficeCoListOfficeName` VARCHAR(50) COMMENT 'Co-Listing Office Name',
`OfficeCoListPhone` VARCHAR(25) COMMENT 'Co-Listing Office Phone',
`OfficeListCode` VARCHAR(8) COMMENT 'Listing Office Code',
`OfficeListCompanyID` VARCHAR(8) COMMENT 'Listing Office Company ID',
`OfficeListID` VARCHAR(8) COMMENT 'Listing Office ID',
`OfficeListIsIDX` CHAR(1) COMMENT 'Listing Office Is IDX',
`OfficeListOfficeName` VARCHAR(50) COMMENT 'Listing Office Name',
`OfficeListPhone` VARCHAR(25) COMMENT 'Listing Office Phone',
`OpenHouseType` VARCHAR(50) COMMENT 'Open House',
`OtherRestrictions` TEXT COMMENT 'Other Restrictions',
`OtherRoomOneDesc` VARCHAR(50) COMMENT 'Other Room 1 Description',
`OtherRoomOneDim` VARCHAR(5) COMMENT 'Other Room 1 Dimensions',
`OtherRoomTwoDesc` VARCHAR(50) COMMENT 'Other Room 2 Description',
`OtherRoomTwoDim` VARCHAR(5) COMMENT 'Other Room 2 Dimensions',
`OvenDesc` VARCHAR(50) COMMENT 'Oven Description',
`OvenSource` VARCHAR(50) COMMENT 'Oven Source',
`PermitAvmUse` CHAR(1) COMMENT 'Permit AVM Use',
`PermitExternalComments` CHAR(1) COMMENT 'PermitExternalComments',
`PictureCount` INT(2) COMMENT 'Picture Count',
`PrimaryPictureURL` VARCHAR(65) COMMENT 'PrimaryPictureURL',
`PropertyClassID` VARCHAR(50) COMMENT 'Property Class',
`PropertySubType` VARCHAR(50) COMMENT 'Property Sub-Type',
`RangeDesc` VARCHAR(50) COMMENT 'Range Description',
`RangeSource` VARCHAR(50) COMMENT 'Range Source',
`RecRoomDesc` VARCHAR(50) COMMENT 'Rec Room Description',
`RecRoomDim` VARCHAR(5) COMMENT 'Rec Room Dimensions',
`Remarks` VARCHAR(255) COMMENT 'Public Remarks',
`RentingPermitted` CHAR(1) COMMENT 'Renting Permitted',
`RoofMaterial` VARCHAR(50) COMMENT 'Roofing Material',
`SecurityDesc` TEXT COMMENT 'Security Description',
`SewerSystem` VARCHAR(50) COMMENT 'Sewer System',
`SqFtBasement` INT(6) COMMENT 'Sq. Ft. Basement',
`SqFtMainFloor` INT(6) COMMENT 'Sq. Ft. Main Floor',
`SqFtMeasurementSource` VARCHAR(50) COMMENT 'Sq. Ft. Measurement Source',
`SqFtOther` INT(6) COMMENT 'Sq. Ft. Other',
`SqFtOtherDescription` VARCHAR(50) COMMENT 'Other Sq Ft Description',
`SqFtSecondFloor` INT(6) COMMENT 'Sq. Ft. Second Floor',
`SqFtThirdFloor` INT(6) COMMENT 'Sq. Ft. Third Floor',
`SqFtTotal` INT(6) COMMENT 'Sq. Ft. Total',
`State` VARCHAR(50) COMMENT 'State',
`StreetAddressDisplay` VARCHAR(75) COMMENT 'Street Address',
`StreetDirection` VARCHAR(50) COMMENT 'Street Direction',
`StreetLetter` VARCHAR(2) COMMENT 'Street Letter',
`StreetName` VARCHAR(26) COMMENT 'Street Name',
`StreetNumber` INT(10) COMMENT 'Street Number',
`Style` VARCHAR(50) COMMENT 'Style',
`Subdivision` VARCHAR(26) COMMENT 'Development',
`TaxAmount` INT(10) COMMENT 'Tax Amount',
`TotalBedrooms` INT(2) COMMENT 'Total Bedrooms',
`TotalFullBaths` INT(2) COMMENT 'Total Full Baths',
`TotalHalfBaths` INT(2) COMMENT 'Total Half Baths',
`TotalRooms` INT(2) COMMENT 'Total Rooms',
`TrailersPermitted` CHAR(1) COMMENT 'Trailers Permitted',
`UnitLocation` VARCHAR(50) COMMENT 'Unit Location',
`UnitNumber` VARCHAR(10) COMMENT 'Unit Number',
`VerticalLocation` VARCHAR(50) COMMENT 'Vertical Location',
`WaterfrontDesc` VARCHAR(50) COMMENT 'Waterfront Description',
`WaterfrontDock` VARCHAR(50) COMMENT 'Waterfront Dock',
`WaterSource` VARCHAR(50) COMMENT 'Water Source',
`WithdrawnDate` DATE default '0000-00-00' not null COMMENT 'Withdrawn Date',
`YearBuilt` INT(4) COMMENT 'Year Built',
`ZipCode` VARCHAR(5) COMMENT 'Zip Code',
PRIMARY KEY(`MlsNum`) );

我需要将上面的SQL重新排序成的新订单:

BedroomsThird
OtherRoomOneDesc
KitchenDim
BedroomTwoDim
Subdivision
OfficeListOfficeName
DiningRoomDim
CoolingSystem
OvenSource
OtherRoomOneDim
AgentCoListFullName
FloorTypes
PictureCount
HobbyRoomDim
BedroomFourDim
OfficeListPhone
FullBathsOther
UnitLocation
OtherRestrictions
SqFtOtherDescription
OfficeCoListOfficeName
SqFtTotal
OfficeListCompanyID
OvenDesc
ListingType
TotalFullBaths
HalfBathsBasement
InteriorOther
OfficeCoListPhone
AppliancesOther
GreenCertifyingBody
ListPrice
CountyID
OfficeListCode
BasementType
BedroomsBasement
HalfBathsThird
HeatingSystem
StreetLetter
RangeDesc
TotalHalfBaths
RentingPermitted
AreaID
BuiltInfo
HighSchool
PermitExternalComments
OfficeCoListID
YearBuilt
AgentListFirstName
Directions
CoolingSource
OfficeListIsIDX
NextOpenHouseDate
ElementarySchool
AgentCoListFirstName
LivingRoomDim
WaterfrontDesc
WaterSource
Remarks
AgentCoListID
ExteriorAlterations
City
ConstructionType
DiningRoomDesc
CommunityAmenities
MapNumber
OfficeListID
TrailersPermitted
TotalBedrooms
PropertyClassID
LivingRoomDesc
DenDesc
MailboxType
HalfBathsMain
OfficeCoListCode
PropertySubType
DrapesRemain
SqFtBasement
FullBathsMain
AgentListLastName
AgentCoListPhone
BedroomTwoDesc
OpenHouseType
FullBathsSecond
RecRoomDesc
SqFtSecondFloor
SewerSystem
BedroomsOther
ModDate
BedroomsSecond
StreetDirection
MlsNum
ExteriorTrim
JuniorHighSchool
IsNewConstruction
State
GarageCapacity
SqFtMeasurementSource
VerticalLocation
HalfBathsSecond
KitchenDesc
ElementarySchoolTwo
WaterfrontDock
HasPetRestriction
StreetAddressDisplay
DenDim
UnitNumber
OfficeCoListCompanyID
AgentListID
ZipCode
EnergyFeatures
OtherRoomTwoDesc
RangeSource
FullBathsBasement
RecRoomDim
TotalRooms
AgentListFullName
WithdrawnDate
BedroomOneDesc
AssociationFeeIncludes
Style
DrivewayType
OtherRoomTwoDim
HobbyRoomDesc
BedroomsMain
SecurityDesc
HandicapAccess
SqFtMainFloor
AgentCoListLastName
ListingStatusID
BedroomOneDim
MiscOther
IsCertifiedGreen
PrimaryPictureURL
NumOfStories
TaxAmount
SqFtThirdFloor
ContingencyType
StreetNumber
NumberOfFireplaces
BasementDesc
Latitude
AssociationFee
CompletionDate
PermitAvmUse
MediaUpdateDate
HeatingSource
RoofMaterial
FullBathsThird
SqFtOther
Longitude
StreetName
GarageDescription
HalfBathsOther
AgentListPhone
BedroomThreeDim

我最终使用命令行来执行此操作。这是我使用的:

mysqlimport -hHOSTNAMEm --user=USERNAME -pPASSWORD --ignore-lines=1 --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --lines-terminated-by="\n" --local DBANAME rets_property_cnd.csv -c BedroomsThird,OtherRoomOneDesc,KitchenDim,BedroomTwoDim,Subdivision,OfficeListOfficeName,DiningRoomDim,CoolingSystem,OvenSource,OtherRoomOneDim,AgentCoListFullName,FloorTypes,PictureCount,HobbyRoomDim,BedroomFourDim,OfficeListPhone,FullBathsOther,UnitLocation,OtherRestrictions,SqFtOtherDescription,OfficeCoListOfficeName,SqFtTotal,OfficeListCompanyID,OvenDesc,ListingType,TotalFullBaths,HalfBathsBasement,InteriorOther,OfficeCoListPhone,AppliancesOther,GreenCertifyingBody,ListPrice,CountyID,OfficeListCode,BasementType,BedroomsBasement,HalfBathsThird,HeatingSystem,StreetLetter,RangeDesc,TotalHalfBaths,RentingPermitted,AreaID,BuiltInfo,HighSchool,PermitExternalComments,OfficeCoListID,YearBuilt,AgentListFirstName,Directions,CoolingSource,OfficeListIsIDX,NextOpenHouseDate,ElementarySchool,AgentCoListFirstName,LivingRoomDim,WaterfrontDesc,WaterSource,Remarks,AgentCoListID,ExteriorAlterations,City,ConstructionType,DiningRoomDesc,CommunityAmenities,MapNumber,OfficeListID,TrailersPermitted,TotalBedrooms,PropertyClassID,LivingRoomDesc,DenDesc,MailboxType,HalfBathsMain,OfficeCoListCode,PropertySubType,DrapesRemain,SqFtBasement,FullBathsMain,AgentListLastName,AgentCoListPhone,BedroomTwoDesc,OpenHouseType,FullBathsSecond,RecRoomDesc,SqFtSecondFloor,SewerSystem,BedroomsOther,ModDate,BedroomsSecond,StreetDirection,MlsNum,ExteriorTrim,JuniorHighSchool,IsNewConstruction,State,GarageCapacity,SqFtMeasurementSource,VerticalLocation,HalfBathsSecond,KitchenDesc,ElementarySchoolTwo,WaterfrontDock,HasPetRestriction,StreetAddressDisplay,DenDim,UnitNumber,OfficeCoListCompanyID,AgentListID,ZipCode,EnergyFeatures,OtherRoomTwoDesc,RangeSource,FullBathsBasement,RecRoomDim,TotalRooms,AgentListFullName,WithdrawnDate,BedroomOneDesc,AssociationFeeIncludes,Style,DrivewayType,OtherRoomTwoDim,HobbyRoomDesc,BedroomsMain,SecurityDesc,HandicapAccess,SqFtMainFloor,AgentCoListLastName,ListingStatusID,BedroomOneDim,MiscOther,IsCertifiedGreen,PrimaryPictureURL,NumOfStories,TaxAmount,SqFtThirdFloor,ContingencyType,StreetNumber,NumberOfFireplaces,BasementDesc,Latitude,AssociationFee,CompletionDate,PermitAvmUse,MediaUpdateDate,HeatingSource,RoofMaterial,FullBathsThird,SqFtOther,Longitude,StreetName,GarageDescription,HalfBathsOther,AgentListPhone,BedroomThreeDim

在 mysqlimport 的语法中，我一直遗漏了一个技巧。 CSV 文件的名称必须与您的表格相同。

Answer 1

您不能只在 LOAD DATA 中指定列顺序吗？声明？